Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
brettg
Helper II
Helper II

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

 

 

powerbiii.PNG

 

 

 

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

 

Hi @v-shex-msft

 

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft

 

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

 

 

powerbi-formula.JPG

 

 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.