How to filter table with latest day, dynamically?

[alexbjorlig]

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);

 

 

[Fowmy · Accepted solution]

@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 🚀

[KasunManchanaya]

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.

[KasunManchanaya]

Okay I will check and get back to you.

[KasunManchanaya]

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 =

SELECTEDVALUE('fact'[subject])

return

CONCATENATEX(

FILTER(

'fact',

'fact'[Max Y/N] = "N"

),'fact'[subject])

[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.

[KasunManchanaya]

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.

[KasunManchanaya]

Mark and solved if this solve your query.