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 table shows the users latest test within the date range.

 

My pie chart is currently running off the normal grade column, showing all the tests. I would like this pie chart to only show the lastest grade for each user, but it won't let me add the latest grade as a legend or value..

 

Would anyone be able to help with this?

 

Thanks in advance

 

 

 

 

 

  • 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

6 Replies

  • 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