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.
I am trying to create a graph that looks at how many users are in Top/Bottom/Middle tier based on a ranking system we have, but as these change on a daily basis i need to have a calculation that that looks at a specific user ID at gives me the most recent User tier by date. so i only want to see the latest tier based on the date i select. Below is some sample data, each user will appear multiple times with different user levels by date;
ID | Username | User ID | User Level | Date |
11111 | Steve | 186964 | Top | 05 February 2020 |
22222 | John | 570676 | Bottom | 06 February 2020 |
3333 | Mary | 343110 | Middle | 07 February 2020 |
4444 | Claire | 1027573 | Top | 25 February 2020 |
55555 | Chris | 55175 | Bottom | 18 February 2020 |
Solved! Go to Solution.
Hi @Anonymous ,
You may create measure like DAX below.
Count User per tier =
VAR _MaxDate =
CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[User Level] ) )
RETURN
CALCULATE (
COUNT ( Table1[User ID] ),
FILTER ( ALLEXCEPT ( Table1, Table1[User Level] ), Table1[Date] = _MaxDate )
)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You may create measure like DAX below.
Count User per tier =
VAR _MaxDate =
CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[User Level] ) )
RETURN
CALCULATE (
COUNT ( Table1[User ID] ),
FILTER ( ALLEXCEPT ( Table1, Table1[User Level] ), Table1[Date] = _MaxDate )
)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try to create columns like
for use level lastnonblankvalue(table[Date],max(table[User Level]))
use max date if needed
@amitchandak i need this to be a measure so when i move dates on a filter it will go to the latest base don that date hence why i cant use a calculated column
@Anonymous if a user has two tiers on the same date, which Tier you will pick, let's say Chris has two-values - bottom and top on the same date (or it will never happen) then what Tier will be used?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |