Forum Discussion
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
| Country | Date | Qty |
| UK | 01/09/2020 | 10 |
| UK | 01/10/2020 | 9 |
| Germany | 01/08/2020 | 4 |
| Germany | 01/09/2020 | 11 |
| UAE | 01/07/2020 | 6 |
Desired Table
| Country | Date | Qty |
| UK | 01/10/2020 | 9 |
| Germany | 01/09/2020 | 11 |
| UAE | 01/07/2020 | 6 |
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..
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 ) )Check the modified sample file in the below, hopes to help you.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
13 Replies
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 )Attached a sample file in the below, hopes to help you.
Best Regards,
Yingjie LiIf 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?
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.
