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
Anonymous
Not applicable

Selecting 'last value' but based on a different column

Hello, I was hoping anyone could help me with this problem. See this 3x3 dataset in Query Editor:

Alex_Brown_0-1610712910176.png

When I want to create a table out of this in the report view, I would like to show only the row with the most recent date.

So, ideally, it would say:

 

Alex_Brown_1-1610713116960.png

However, when I select 'most recent date' under values in report view, it still shows all three rows, like this:

Alex_Brown_2-1610713214974.png

 

If I select to only show 'Last letter', it shows the c, not the a (probably because of alphabetical order):

Alex_Brown_4-1610713295024.png

 

How do I change this? I want the 'last letter' do display a, because that's the letter that belongs to the most recent date, not c.

 

Hopefully I've explained myself well! If not, please let me know.

 

Lex

1 ACCEPTED SOLUTION

@Anonymous 

You can add a column to your table to flag the latest record per id as follows:

Flag = 
IF(
    T14[date] = 
    MAXX(
        FILTER(T14, T14[id] = EARLIER(T14[id])),
        T14[date]
    ),
    1,
    0
)

 

Then, assign to the visual filter and set equal to 1

Fowmy_0-1610720208208.png

 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
Fowmy
Super User
Super User

@Anonymous 

You can use the TOPN option and assign one on the Filter Pane for Visual Filter

 

Fowmy_0-1610715256505.png

 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Hi Fowmy, thank you for your help. This seems to work for a single unique ID, but what if the table is to be expanded like this: 

 

Alex_Brown_0-1610716730389.png

And you want to show the most recent date per id with the corresponding letter? It would show like this:

Alex_Brown_1-1610716850461.png

 

@Anonymous 

You can add a column to your table to flag the latest record per id as follows:

Flag = 
IF(
    T14[date] = 
    MAXX(
        FILTER(T14, T14[id] = EARLIER(T14[id])),
        T14[date]
    ),
    1,
    0
)

 

Then, assign to the visual filter and set equal to 1

Fowmy_0-1610720208208.png

 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

This worked! Thank you kind sir.

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.