topic Re: Filter a page with date less than max date from Fact table in DAX Commands and Tips

Filter a page with date less than max date from Fact table

bol — Mon, 12 Feb 2024 16:09:25 GMT

Hello,

I am new to DAX, just received a two days training overall on PBI after a few months struggling for basically anything related to DAX.

My Data model contains several fact tables. One table with 'FactA'[Date] data in 2024, another with 'FactB'[Date] only up to 2023.

I want to create a page specific to the 'FactB' (max 2023).

I also want to calculate anything using my Date table to keep it at the center of any calculation involving dates.

If I create a segment with 'Date'[Date], I will have 2024 has the max date.

For instance I have several measures :

MaxYear = YEAR(MAXX(allselected('Date'[Date]),'Date'[Date]))

MaxYear-1 = [MaxYear]-1

MaxYear-2 = [MaxYear]-2

If I put MaxYear in a card, by default it display 2024. I wish in my case that it displays 2023.

Obviously I want MaxYear-1 & MaxYear-2 to follow.

I tried a trick with a filter like TableB[id] is not null without success.

I will clarify if necessary.

I looked in several other posts but somehow didn't find something related. Happy to know if you are more lucky.

Re: Filter a page with date less than max date from Fact table

lbendlin — Tue, 13 Feb 2024 00:54:40 GMT

What's preventing you from using the filter pane "Filters on this page" ?

Re: Filter a page with date less than max date from Fact table

bol — Tue, 13 Feb 2024 07:48:22 GMT

Nothing is preventing me from using it except the way to use it or my skill.

Which measure should I put there? 'MaxYear' ?
Which criteria should I set on that measure? 'less than' what ?

I am looking for a dynamic solution. I don't want to set a constant otherwise I will have to change it (or the user) as soon as there will be data in 2024.

Re: Filter a page with date less than max date from Fact table

bol — Tue, 13 Feb 2024 09:14:23 GMT

I am preparing an example.

In parallel I will add a flag in my date table to tag all dates where there is data in FactB. I will filter on that tag afterward.

Re: Filter a page with date less than max date from Fact table

bol — Tue, 13 Feb 2024 09:35:44 GMT

Here is the example.

In page ViewAll, no filter. In page View B, I filtered manually with date less than 2024. I wish to to make that filter automatic to max FactB[Date].

MaxDateTableB.pbix

Re: Filter a page with date less than max date from Fact table

lbendlin — Tue, 13 Feb 2024 22:25:48 GMT

see attached.

NOTE1 : This is a measure filter on a visual. If your data sources are in import mode you could add a calculated column to the calendar table instead and make it a page or report filter.

NOTE2 : You are comparing filter context to scalar value. I chose the MIN aggregation to include the entire filter context. You could use MAX instead to cut off exactly at the date.

Re: Filter a page with date less than max date from Fact table

bol — Fri, 16 Feb 2024 08:44:38 GMT

Thank you for the measure. It helped to save some time instead of figuring it out by myself.
I added it to my calendar table, and after fixing my dates columns it worked nicely as a filter.