Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to ask for help with the following:
I'd like to calculate cumulative result with TOTALYTD. The topic is testing.
I have the following table:
id | test execution date | status |
1 | 2020.01.01 | failed |
1 | 2020.01.02 | success |
2 | 2020.01.01 | success |
I have this calculation:
CALCULATE(TOTALYTD(DISTINCTCOUNT('table'[id]),dim_date[Date]))
The result I get is for 2020.01.01 is 1 failed and 1 success (sum 2), which is fine.
The problem is for 2020.01.02, where my cumulative result is 1 failed and 2 success (sum 3). But I would like to get 2 successes, as the 'id-1' was tested again and turned out to be successful) and id-2 as successful.
So basically if an [id] was tested again I don't want to add it to a cumulative results, just the last tested one.
Thanks for your help in advance.
Kind regards,
Benjamin
Solved! Go to Solution.
Hi @kormosb ,
Based on your description, you can create some measures as follows.
Latest Execution Status =
var x1=MAXX(FILTER(ALL(table4),[id]=SELECTEDVALUE(table4[id])),[test execution date])
return
MAXX(FILTER(ALL(table4),[id]=SELECTEDVALUE(table4[id])&&[test execution date]=x1),[status])
Latest execution date = MAXX(FILTER(ALL(table4),[status]=[Latest Execution Status]&&[id]=SELECTEDVALUE(table4[id])),[test execution date])
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @kormosb ,
Based on your description, you can create some measures as follows.
Latest Execution Status =
var x1=MAXX(FILTER(ALL(table4),[id]=SELECTEDVALUE(table4[id])),[test execution date])
return
MAXX(FILTER(ALL(table4),[id]=SELECTEDVALUE(table4[id])&&[test execution date]=x1),[status])
Latest execution date = MAXX(FILTER(ALL(table4),[status]=[Latest Execution Status]&&[id]=SELECTEDVALUE(table4[id])),[test execution date])
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @kormosb ,
You may try:
Hey,
The table was a short example, I am not trying to calculate the cumulative result of the id-1, I would like a general sulition. I definitely need a cumulative DAX formula, something like totalytd.
Kind regards,
Benjamin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |