Hey @jr3151006 ,
thanks for the sample files.
Before I explain the steps that lead to this solution:
It's important to remember that a dynamic table, dynamic in the sense that it reflects the current filter context, is not possible and can not be created. DAX-based tables will only be calculated when the data model is refreshed. This means it is not possible to create the table "Table - with groupby."
Virtual tables (tables created inside a DAX measure) reflect the current filter context.
All this means, it's only possible to filter an existing table accordingly, here it's the table - Table.
The measure I created for the filtering:
flagLastDate =
var filtertable =
ADDCOLUMNS(
SUMMARIZE(
'Table'
, 'Table'[LicensePlate]
)
, "MaxDate" , CALCULATE( MAX( 'Table'[Date] ) , ALLSELECTED( 'Table'[Date]) )
)
return
COUNTX(
'Table'
, IF( CONTAINSROW( filtertable , 'Table'[LicensePlate] , 'Table'[Date] )
, 1
, BLANK()
)
)
This measure has two parts
- create a table "filtertable" that contains only the license plates with the MAX date considering the current selection from the DateDimension table
- Checking if the current row from the table Table is part of the filtertable. This check is done using the CONTAINSROW function inside an IF. If the current row or being more precise only the license plate and the date of the current row are part of the filtertable, then 1 is returned, otherwise BLANK(). As no specific aggregate function is required I use COUNTX.
This measure can be used as filter on the table visual - a visual level filter:
Hopefully, this provides what you are looking for.
Regards,
Tom
I think, if I understand correctly, you need to add a month column to your data, and include it in the grouping columns.
That way, your MAX date will get the max for the new Month column context as well, not over all months.
| Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |  |
| If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos.  | Proud to be a Super User! |  |
I didn't read the filtering portion of your question well enough.
@TomMartens is correct, you need the measure so that it is calculated on the fly.
Create a sample PBIX and share it as Tom suggested. You will get an answer much quicker that way.
| Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). | |
| If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! | |
After some tests and research, I found that the 'Latest' feature on the columns, applied directly to the main 'TABLE' dataset (without need to use 'groupby' feature), shows the desired results.
🤔
* Now the research continues in a way to maintain that behavior directly on the table - or even on a new 'virtual table' created via DAX.
Helpful resources
March 2023 Events
Find out more about the online and in person events happening in March!
