Forum Discussion
alexbjorlig
Helper IV
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 bu...
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 )
First you need to create a calculated Colun in the fact table with this.
Max Y/N =
var __seldate = 'fact'[date]
var __selcate = 'fact'[category]
var __maxdate =
CALCULATE(
MAX('fact'[date]),
FILTER(
'fact',
'fact'[category] = __selcate
))
return
IF(__seldate>=__maxdate,"N","Y")
Then use this measure
Thank you.
Latest Data =
var __selsubject =
var __selsubject =
SELECTEDVALUE('fact'[subject])
return
CONCATENATEX(
FILTER(
'fact',
'fact'[Max Y/N] = "N"
),'fact'[subject])
Mark and solved if this solve your query.