Measure help

[Anonymous]

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

[v-xicai · Accepted 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.

[amitchandak]

@Anonymous , Try to create columns like

for use level  lastnonblankvalue(table[Date],max(table[User Level]))

use max date if needed

[Anonymous]

@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

[parry2k]

@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?

[Anonymous]

@parry2k no you would only have 1 tier for 1 date