Using a users latest value in a pie chart

[brettg]

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]

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]

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]

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]

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 · Accepted solution]

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]

@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