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,

 

You can write a measure to check current date and return tag, then drag this measure to visual level filter of pie chart with 'is' mode to filter matched records.

IsLast =
VAR currDate =
    MAX ( 'Table'[Test Date] )
VAR _lastDate =
    CALCULATE (
        MAX ( 'Table'[Test Date] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[User] )
    )
RETURN
    IF ( currDate = _lastDate, "Y", "N" )

 

Regards,

Xiaoxin Sheng

brettg's avatar
brettg
Helper II
8 years ago

 

Hi Anonymous

 

Thank you for your response.

 

This measure seems to return "Y" for all the results for me. 

 

Is there anyway to make this measure use distinct users, and take their latest test in the date range slider?

 

Thanks,

Brett

  • 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