Forum Discussion

brettg's avatar
brettg
Helper II
8 years ago
Solved

Using a users latest value in a pie chart

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 tabl...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

brettg's avatar
brettg
Helper II
7 years ago

Anonymous

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