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 am currently working on a measure using the following table:
Number | Item | Reason Code | Status | Center |
60607788 | abc | 0B | 10 | G1A30 |
60607788 | abc | 0B | 20 | G1G30 |
60607788 | abc | 0B | 30 | G1C30 |
60607788 | abc | 0B | 40 | G1T40 |
60607788 | abc | 0B | 50 | G1X50 |
60607788 | abc | 0B | 60 | G1S20 |
60607788 | abc | 0B | 70 | G1R40 |
60607788 | abc | 0B | 80 | G1L30 |
60607788 | abc | 0B | 90 | G1H40 |
60607789 | bbc | 0B | 10 | G1A30 |
60607789 | bbc | 0B | 20 | G1G30 |
60607789 | bbc | 0B | 30 | G1C30 |
60607789 | bbc | 0B | 40 | G1T40 |
60607789 | bbc | 0B | 50 | G1X50 |
60607789 | bbc | 0B | 60 | G1S20 |
60607789 | bbc | 70 | G1R40 | |
60607789 | bbc | 80 | G1L30 | |
60607789 | bbc | 90 | G1H40 |
I am using the following measure:
Last CC =
VAR MaxStatus = MAX('Work Order Time Transactions'[Status])
Return
MAXX(
FILTER(
'Work Order Time Transactions',
MaxStatus
&& 'Work Order Time Transactions'[Reason Code] = "0B"
),
'Work Order Time Transactions'[Center]
)
The measure works but does not give me the desired result. For the table above I want to know the last Center for maximum Status and reason Code 0B. In the table, the results for abc should be G1H40 since the maximum Status with reason Code 0B is 90, but the measure computes G1X50 since the string is the biggest. I found the gap in my measure but I am struggling to solve it.
I gladly appreciate any hints to solve this issue.
Thanks & Best regards
Hansson
Solved! Go to Solution.
MAXCC =
VAR maxStatus =
CALCULATE (
MAX ( 'Work Order Time Transactions'[Status] ),
ALLEXCEPT (
'Work Order Time Transactions',
'Work Order Time Transactions'[Item]
),
'Work Order Time Transactions'[Reason Code] = "0B"
)
RETURN
CALCULATE (
SELECTEDVALUE ( 'Work Order Time Transactions'[Center] ),
'Work Order Time Transactions'[Status] = maxStatus
)
Hi Colacan,
thanks for your approach. I had to modify it though, with the Allexcept it did not work. However once I removed that filter it showed the same results.
Best regards
Hansson
@hanssonnor Hi Hanssonnor, I have noticed that extracting highest or lowest recode within categories is quite tipical problem which lots of people are facing. And Allexcept function is quite useful for those problems.
Cheers!
@hanssonnor , Try a measure like
calculate(lastnonblankvalue('Work Order Time Transactions'[Status], max('Work Order Time Transactions'[Center])),'Work Order Time Transactions'[Reason Code] = "0B")
Hi Amit,
thanks a lot, it works 🙂
Best regards
Arne
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |