Forum Discussion
Helper IVHow to filter table with latest day, dynamically?
Hi everyone. Still trying to understand the basics of CALCULATE and filter context.
This problem is really hard for me to solve, so looking forward to your input/solutions.
My objective is to build tables, that only include latest data for each category;
With no filters, I wan't the latest row for each category:
And if filtered on District=z, I want:
Here is a workbook with the data/tables - thanks in advance!
What I have
My current attempt is to add a measure, and then use it as visual filter:
IsLatest = MAXX(
'fact',
VAR Category = 'fact'[category] RETURN
VAR Latest = CALCULATE(MAX('fact'[date]), ALLSELECTED(),'fact'[category] == Category) RETURN
IF('fact'[date] == Latest, 1, 0)
)
But it does not return the correct value (here filtered on distrcit=z);
alexbjorlig
You have built the correct logic but you don't need to iterate over the fact table.
I modified your measure:Latest = VAR __category = MAX('fact'[category]) VAR __maxdate = CALCULATE( MAX('fact'[date] ) , ALLSELECTED('fact' ) , 'fact'[category] = __category ) return INT ( max('fact'[date]) = __maxdate )
9 Replies
COuld you please provide more information. Also along with your PBI file.
- alexbjorlig
More information - what whould you like to know more?
As described the post, the file is here.
Okay I will check and get back to you.
- Fowmy
Super User
alexbjorlig
You have built the correct logic but you don't need to iterate over the fact table.
I modified your measure:Latest = VAR __category = MAX('fact'[category]) VAR __maxdate = CALCULATE( MAX('fact'[date] ) , ALLSELECTED('fact' ) , 'fact'[category] = __category ) return INT ( max('fact'[date]) = __maxdate )- alexbjorlig
Thanks man - amazing when the solution turn out to be more simple than expected 🚀
