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 )
Okay I will check and get back to you.
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.
- alexbjorlig
Hi KasunManchanaya - thanks for giving it a try, but does not seem to work.
I don't think it's possible to use a calculated column, because the value is only calculated once, and does not dynamically adopt.
If there is a filter requirement CC will not help you. then you have to do it in the measure itself. I will give it a try and let you know.
