Forum Discussion
Measure to calculate current closed risks
Hi, I need help with a measure to calculate current closed risks. I have the risk table linked to the calendar[Date] table with an active relationship in Creation Date and one inactive for Mitigation Date (Closing risk date).
The measure I use is the following:
Mitigated risks = var selectedDate = Max('Calendar[Date]) VAr FilteredTable = Filter('Risk', 'Risk'[Mitigation Date] <= selectedDate && 'Risk'[Mitigated]="Mitigated") RETURN Countrows(FilteredTable)
This measure works ok, but when I use it in a visual and filter by a slicer with Calendar[Date] as the field, it behaves strangely.
If I select the first semester (H2 2021), it displays the correct count of closed risks,
if I select the next semester (H1 2022), it goes blank and it is blank for all subsequent semesters.
This shows that the measure works with the first date in the slicer:
This is to show that starting from the next semester in the slicer, the measure goes blank.
What is missing in the measure so that it displays the correct count for each semester?
Any help is appreciated.
Edit: if I add 'ALL' to the measure, it works ok for the semester. But if I add it to bar chart with risk owner on the other axis, I get the same total per each user.
Mitigated risks = var selectedDate = Max('Calendar[Date]) VAr FilteredTable = Filter(ALL('Risk'), 'Risk'[Mitigation Date] <= selectedDate && 'Risk'[Mitigated]="Mitigated") RETURN Countrows(FilteredTable)
Tables:
| Risk Id | Creation semester | Creation Date | Mitigation Semester | Mitigation Date | Mitigated |
| 1 | 2021 H2 | 31/12/2021 | 2022 H1 | 30/06/2022 | Mitigated |
| 2 | 2021 H2 | 31/12/2021 | 2021 H2 | 31/12/2021 | Mitigated |
| 3 | 2022 H1 | 30/06/2022 | 2022 H1 | 30/06/2022 | Mitigated |
| 4 | 2022 H1 | 30/06/2022 | 2022 H2 | 31/12/2022 | Mitigated |
| 5 | 2022 H2 | 31/12/2022 | Open | ||
| 6 | 2022 H2 | 31/12/2022 | 2023 H1 | 30/06/2023 | Mitigated |
Solved with the following dax:
= CALCULATE([Total Mitigated Risks], DATESBETWEEN('Calendar'[Date],BLANK(),MAX('Calendar'[Date])))where Total Mitigated risks =CALCULATE(COUNT('Risks'[Mitigated]),'Risks'[Mitigated]="Mitigated", USERELATIONSHIP('Calendar'[Date],'Risks'[Mitigation Date]))+0
5 Replies
Hi mariella2
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523any suggestions please?
any help is much appreacited.
Hi, I am bumping this post again as I still need your help.
I've modified a little bit the measure to this:
Mitigated current risks = VAR MaxDAte = MAX('Calendar'[Date]) REturnCALCULATE(Countrows(Filter(ALL('Risks'), 'Risks'[Mitigation Date] <= MaxDate && 'Risks'[Mitigated]="Mitigated")), USERELATIONSHIP('Calendar'[Date],'Risks'[Mitigation Date]))What happens is that I get the right current number of the mitigated risks for each of the semesters(meaning that the mitigated risks for the first semester are added to the mitigated risks of the next semester, and so on).What I'd like to do with this measure is to show the current mitigated risks for each risk owner, but when I put them into table, it shows the total current mitigated risks for each risk owner.
If I remove the 'ALL' in the measure, I get the mitigated the risks for that semester not in addition to the previous one, which is what I am looking for.
How can I solve it? Thank you in advance for your help.Solved with the following dax:
= CALCULATE([Total Mitigated Risks], DATESBETWEEN('Calendar'[Date],BLANK(),MAX('Calendar'[Date])))where Total Mitigated risks =CALCULATE(COUNT('Risks'[Mitigated]),'Risks'[Mitigated]="Mitigated", USERELATIONSHIP('Calendar'[Date],'Risks'[Mitigation Date]))+0
