Forum Discussion
Helper IVHow do I filter table, based on latest date in related table
I'm trying to learn DAX, and have watched this great video explaining filter context and row context.
However I'm still struggeling to achive the objective; Filter Sales table to only include
Sales
| Type | ReportId | value |
| 123 | A | 5 |
| 123 | A | 6 |
| 123 | B | 2 |
Reports
| ReportId | PublishDate |
| A | 1 jan 2020 |
| B | 6 feb 2021 |
Desired result
| Type | ReportId | value |
| 123 | B | 2 |
Thanks for your help.
Oh ok! Now I get it...
Ok, so you can use the following as a variables:
Filter as variable = VAR MaxD = CALCULATE(MAX(Reports[PublishDate]), ALL(Reports)) VAR SalesRows = FILTER(Reports, Reports[PublishDate] = MaxD) RETURN .....Expression(Just beware that the SalesRows delivers a table)
You can also create a measure to filter visuals using:
Filter rows for max publish date = VAR MaxD = CALCULATE(MAX(Reports[PublishDate]), ALL(Reports)) VAR SalesRows = FILTER(Reports, Reports[PublishDate] = MaxD) RETURN COUNTROWS(SalesRows)and you can add this measure to the filters on the visual, set the value to 1 and it will filter the rows accordingly
9 Replies
PaulDBrownCommunity Champion
There are many ways to do this: using a slicer, a measure, a filter in the filter pane...
Even so, the very first step is setting up the model with dimension tables with the relevant relationships.
how is your model set up?do you want the filtering to be dynamic?
- alexbjorlig
So I wan't this to happen in a measure, the model is star schema and set up with the relationship defined on the ReportId column. The filtering should be dynamic.
- PaulDBrown
Ok, to calculate the values for the most recent report date:
Value on Last published date = VAR MaxD = CALCULATE ( MAX ( Reports[PublishDate] ), ALL ( Reports ) ) RETURN CALCULATE ( SUM ( Sales[value] ), Reports[PublishDate] = MaxD )
