cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alexbjorlig
Advocate II
Advocate II

How 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:

 

Screen Shot 2021-10-26 at 11.34.53.png

And if filtered on District=z, I want:

Screen Shot 2021-10-26 at 11.35.47.png

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

Screen Shot 2021-10-26 at 11.47.55.png

1 ACCEPTED SOLUTION
Fowmy
Super User
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 )




Fowmy_0-1635256312986.pngFowmy_1-1635256328268.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

9 REPLIES 9
Fowmy
Super User
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 )




Fowmy_0-1635256312986.pngFowmy_1-1635256328268.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Thanks man - amazing when the solution turn out to be more simple than expected 🚀

KasunManchanaya
New Member

COuld you please provide more information. Also along with your PBI file.

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.

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

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.

 

Screen Shot 2021-10-26 at 14.16.56.png

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.

Mark and solved if this solve your query.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.