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

brettg's avatar
brettg
Helper II
7 years ago

Hi Anonymous

 

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

 

 

 

 

 

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