Forum Discussion
mariella2
Helper I
Helper IMeasure 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...
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
mariella2Helper I
Helper IHi, 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]) REturn
CALCULATE(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.
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.