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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
StuartSmith
Power Participant
Power Participant

Latest Date from Multiple Rows

I have a table visual similar to the below and need to filter the table, so it only shows the latest for each coumtry.  

 

Unfiltered Table

CountryDateQty
UK01/09/202010
UK01/10/20209
Germany01/08/20204
Germany01/09/202011
UAE01/07/20206

 

Desired Table

 

CountryDateQty
UK01/10/20209
Germany01/09/202011
UAE01/07/20206

 

I have tried "Latest Date" from the "Invoice Date" value on the table, but  that either doesnt work or do anything as i still have multiple rows for each country.

 

I have tried a "Top N" = 1 visual filter, but that then only shows the most recent month such as October, missing out countries that have a date of September or August.  I tried TOP N = 2, but then that shows multiple country rows.

 

How can I get just the lastest date showing for each country, when they have different latest dates.

 

****************************************************************

I always search and refer to my previous questions, before asking on here 😀, but my boss is constantly thinking up more and more weird and wonderful report requirements.. 

1 ACCEPTED SOLUTION

Hi @StuartSmith ,

To make the card visual show the same total in the table visual, you can create another measure to calculate it and put it in the card visual:

Sum of Qty = 
CALCULATE ( SUM ( 'Table'[Qty] ), FILTER ( ALL ( 'Table' ), [Control] = 1 ) )

qty.png

Check the modified sample file in the below, hopes to help you.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
v-yingjl
Community Support
Community Support

Hi @StuartSmith ,

You can create this measure, put it in the visual filter and set its value as 1:

Control =
VAR _max =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Country] IN DISTINCT ( 'Table'[Country] ) )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Date] ) = _max, 1, 0 )

visual filter.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

@v-yingjl Not to sure if you can assist further, your solution works exactly as I asked, but I have noticed that there are 2 rows that dont have invoice dates and therefore flagged as "0" and therefore not displayed in the table.  I need these rows to be displayed as well.  Is this possible?

Hi @StuartSmith ,

If you want to show the other rows which measure value is 0, just clear the visual filter.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

@v-yingjl sorry, i mean aswell as seeing the "Max" date for each country by filtering "1", I also need to see rows with no date, so, maybe something like setting "Max" = 1, "Blank" ="2". Hope thats clearer.

 

2020-10-19_08-23-03.png

Hi @StuartSmith ,

Try to modify the measure like this:

Control = 
VAR _max =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Country] IN DISTINCT ( 'Table'[Country] ) )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Date] ) = _max || SELECTEDVALUE('Table'[Date]) = BLANK(), 1, 0 )

Basically if uses the previous measure, the blank date should also be displayed.

Check the below sample file.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your continued support (and patience). You additionaly solution worked, and now trying to get the filtered quantity value showing in a cardbut it shows the unfiltered quantity.  I therefore, thought I would be able to apply your measure to the card filter, but it doesnt allow me to use the dropdown options. 

 

2020-10-19_15-28-13.png

Hi @StuartSmith ,

To make the card visual show the same total in the table visual, you can create another measure to calculate it and put it in the card visual:

Sum of Qty = 
CALCULATE ( SUM ( 'Table'[Qty] ), FILTER ( ALL ( 'Table' ), [Control] = 1 ) )

qty.png

Check the modified sample file in the below, hopes to help you.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Thanks, thats great and that also helped me with my calculated table question, and I created a calculated table replicating my invoice table, but only containing the most recent invoice rows.  Then I can refer to the calculated table. 

 

Will the calculated "Invoice Table" automatically update with any changes made on the "Invoice Output" table when the "Invoice Output" table is refreshed.  I believe it does, but just checking.

 

Invoice Table = CALCULATETABLE('Invoice Output', FILTER ( ALL ( 'Invoice Output' ), [Latest Invoice Date] = 1 ) )
 
Once again, thanks for your continued support.
 
 

You are welcome😀.

 

Best Regards,

Yingjie Li

Following on from my earlier post, instead of working with measures to get my data filtered, is there a way to create a calculated\filtered \dynamic table that would show the same result as your measure.  I could then simply refere to the table for cards, charts, etc.

 

I hope that makes sence.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur Thanks.   I will take a look at your solution.

amitchandak
Super User
Super User

@StuartSmith , USe these two measures for date and qty

lastnonblank(Table[Date],blank())


lastnonblankvalue(Table[Date],sum(Table[Qty]))

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors