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 All,
I'm having some difficulty showing a users latest grade in the pie chart below.
As you can see, I have two tables. One table shows all the tests within the date range, the other table shows the users latest test within the date range.
My pie chart is currently running off the normal grade column, showing all the tests. I would like this pie chart to only show the lastest grade for each user, but it won't let me add the latest grade as a legend or value..
Would anyone be able to help with this?
Thanks in advance
Solved! Go to Solution.
HI @brettg,
My formula will return max date based on user, when you use it on summary users, it only return max one form summary users.
I modify my formula to create variable to store summarized value and use current grade to lookup related records.
measure = VAR currGrade = SELECTEDVALUE ( Table[Grade] ) VAR temp = ADDCOLUMNS ( SUMMARIZE ( ALLSELECTED ( Table ), [User], "LastDate", MAX ( Table[Date] ) ), "Grade", LOOKUPVALUE ( Table[Grade], Table[User], [User], Table[Date], [LastDate] ) ) RETURN COUNTAX ( FILTER ( temp, [Grade] = currGrade ), [User] )
Regards,
Xiaoxin Sheng
HI @brettg,
You can write a measure to check current date and return tag, then drag this measure to visual level filter of pie chart with 'is' mode to filter matched records.
IsLast = VAR currDate = MAX ( 'Table'[Test Date] ) VAR _lastDate = CALCULATE ( MAX ( 'Table'[Test Date] ), ALLSELECTED ( 'Table' ), VALUES ( 'Table'[User] ) ) RETURN IF ( currDate = _lastDate, "Y", "N" )
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
Thank you for your response.
This measure seems to return "Y" for all the results for me.
Is there anyway to make this measure use distinct users, and take their latest test in the date range slider?
Thanks,
Brett
HI @brettg,
It seems like your data already summarize sum formula not work properly, maybe you can try to use below formula on value fields.
formula = VAR _lastDate = CALCULATE ( MAX ( 'Table'[Test Date] ), ALLSELECTED ( 'Table' ), VALUES ( 'Table'[User] ) ) RETURN CALCULATE ( COUNT ( Table[Grate] ), FILTER ( ALLSELECTED ( 'Table' ), Table[Date] = _lastDate ), VALUES ( 'Table'[User] ) )
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
Thanks for your reply.
This is what I was looking for, a measure you can use as a value!
But I think this is only taking a max date per grade?
If you see the table I have at the top, it shows the latest grade for each user (Medium, High, Medium)
When I add the formula as a value to my pie chart, it is returning a low grade though?
Thanks for your help with this!
Brett
HI @brettg,
My formula will return max date based on user, when you use it on summary users, it only return max one form summary users.
I modify my formula to create variable to store summarized value and use current grade to lookup related records.
measure = VAR currGrade = SELECTEDVALUE ( Table[Grade] ) VAR temp = ADDCOLUMNS ( SUMMARIZE ( ALLSELECTED ( Table ), [User], "LastDate", MAX ( Table[Date] ) ), "Grade", LOOKUPVALUE ( Table[Grade], Table[User], [User], Table[Date], [LastDate] ) ) RETURN COUNTAX ( FILTER ( temp, [Grade] = currGrade ), [User] )
Regards,
Xiaoxin Sheng
Incredible!
Just what I wanted. I was thinking to myself, surely I can use a temp table like I would on SQL.. and there you have it!
Thank you so much for this solution.
Kind regards,
Brett
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |