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
    8 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
8 years ago

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

brettg's avatar
brettg
Helper II
8 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
    8 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
      8 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