topic Re: Need help filtering data using DAX & TimeSlicer, in DAX Commands and Tips

Need help filtering data using DAX & TimeSlicer,

Anonymous — Fri, 10 Jun 2022 08:31:00 GMT

Hello All,

I have the following User table:

Date, UserKey, Value

01/01/2019,User1,Val1
01/01/2021,User1,Val2
01/01/2022,User1,Val3

01/02/2020,User2,Val1
01/08/2021,User2,Val2
01/10/2022,User2,Val3

01/03/2020,User3,Val1
01/04/2021,User3,Val2
01/06/2021,User3,Val3
01/03/2022,User3,Val4

The Date column is a validity date. Let say for exemple that we are in July 2021, i want to filter the table to only keep the following lines:

User1, 01/01/2021, Val2

User2, 01/02/2020, Val1

User3, 01/06/2021, Val3

For this, I setup a new table, filtering the existing User table with the following DAX Query.

FilteredUser = VAR D1= DATE(2021,07,01)

RETURN FILTER(ADDCOLUMNS(User, "rank", RANKX(FILTER(User, User[Date]<=D1 && [User] = EARLIER([User])), [Date],[Date],DESC,Dense)),[rank]==1 && User[Date]<=D1)

I add a rank column base on the date, and filter only rank==1, removing line also behind the validity.

This is working well using a constant date in the DAX formula.

What I would like now is to put a date slicer into my PBI.

And use the selected value of this slicer in my DAX expression.

I've create for this an independant Date Table. And bind my slicer to this date Table.

I've try to modify my DAX formula with something like:

Table1 = VAR D1= SELECTEDVALUE('Date'[Date])
RETURN FILTER(ADDCOLUMNS(User, "rank", RANKX(FILTER(User, User[Date]<=D1 && [User] = EARLIER([User])), [Date],[Date],DESC,Dense)),[rank]==1 && User[Date]<=D1)

Also try with

VAR D1= CALCULATE ( MAX ( 'Date'[Date] ) )

But find no way to make it work.
It seems that I can't get the slicer value in this context.

Do you have any idea of how I can make this working ?

Here the PBI I use:

https://cegidgroup-my.sharepoint.com/:u:/g/personal/larnal_cegid_com/EY5ekSMiQq5DrXwbRLwXS2MBxfWiitKYA9x53YtJn2DESA?e=4a2QkP

Best regards,

Laurent.

Re: Need help filtering data using DAX & TimeSlicer,

johnt75 — Fri, 10 Jun 2022 08:45:51 GMT

Calculated tables are not affected by slicers or filters as they are only calculated during data refresh. To achieve what you're after you could try creating a measure like

Show Data = IF( SELECTEDVALUE('User'[Date]) <= SELECTEDVALUE('Date'[Date]),1,0)

and use that as a visual filter, to only show when the value is 1

Re: Need help filtering data using DAX & TimeSlicer,

Anonymous — Mon, 13 Jun 2022 08:31:20 GMT

Hello,

Thanks for your quick answer.

Got it about the fact that calculated tables are not affect by slicer, and same for caculated column.

I try using a measure, but can't see how to achieve what I want.

A simple if condition will not work in my case because I want to keep not all line before the date, but the only first one before the date. Let say the date is for exemple 01/05/2021, it will filter for User3 only the line "01/04/2021,User3,Val2".

It was why i've used in my first exemple the Rank function that enable me to give an order on the value to do something like a topN filter. I can't see how I will be able to do the same using a measure.

Laurent.

Re: Need help filtering data using DAX & TimeSlicer,

ManguilibeKAO — Mon, 13 Jun 2022 09:49:40 GMT

Hi,

When you write: " Let say for exemple that we are in July 2021, i want to filter the table to only keep the following lines:

User1, 01/01/2021, Val2

User2, 01/02/2020, Val1

User3, 01/06/2021, Val3", could you you explain your filtering logic (the criteria of filtering)? If the criteria of filtering are clear, we can help you.

Best regards.

Re: Need help filtering data using DAX & TimeSlicer,

Anonymous — Mon, 13 Jun 2022 10:03:41 GMT

To clarify the criteria.

This is a table with historical data.

When you made calculation base on the table, you set a date [dt1] somewhere in the past.

The line to apply then for each user is the first one older to [dt1].

So if I come back to my example, with a [dt1] set to 5/1/2021, you will keep in final result only those lines:

01/01/2021,User1,Val2

01/02/2020,User2,Val1

01/04/2021,User3,Val2

Hopes it make it clearer like this.

Re: Need help filtering data using DAX & TimeSlicer,

v-easonf-msft — Wed, 15 Jun 2022 08:34:14 GMT

Hi， Anonymous

Please try follow steps:
1.create measue as below:

lastdate = VAR D1 = DATE ( 2021, 05, 01 ) RETURN CALCULATE ( MAX ( User[Date] ), FILTER ( ALLEXCEPT ( User, User[User] ), User[Date] <= D1 ) ) //Group by user and get max date less than or equal to current date

visual filter = IF(SELECTEDVALUE(User[Date])=[lastdate],1,0)

2.then drag the measure 'visual filter' to the filter panle of this table visual as below:

Best Regards,
Community Support Team _ Eason

Re: Need help filtering data using DAX & TimeSlicer,

Anonymous — Tue, 28 Jun 2022 14:32:59 GMT

Hello,

Sorry for the delay in my response, was on another stuff last weeks.
I test your solution today, it works great.
Exactly what I need.

Thanks a lot,

Laurent.