topic Re: Customized Date Filtering in DAX Commands and Tips

Customized Date Filtering

Anonymous — Sat, 26 Mar 2022 22:32:55 GMT

Hi,

I was wondering how I would create a measure to exclude the people who leave (have a status of -1) but include everyone with a status of 1 from before but not including the selected date.

I attached a sample power bi file.
https://drive.google.com/file/d/1gyRlYRIZ0KckVOvFBFm6s2tGEq8MvpHM/view?usp=sharing

Thank you!

Re: Customized Date Filtering

lbendlin — Sun, 27 Mar 2022 21:35:47 GMT

Thank you for providing a sample PBIX.

You might be slightly overcomplicating things.

CalenderMaster = CALENDAR("2017-01-01",MAX('data'[date]))

Don't use auto date/time, you already have your own calendar table.

First you need a calculated table with all names

Names = VALUES(data[name])

then a measure that computes the status for each name and the selected date.

date status = var d = CALCULATE(max(data[date]),data[date]<=max(CalenderMaster[Date]),data[name]=SELECTEDVALUE(Names[name])) return calculate(max(data[Status]),data[date]=d,data[name]=SELECTEDVALUE(Names[name]))

and that's it.

NOTE: your calendar definition starts at 2017. Your sample data events start MUCH earlier.

see attached.

Next you need to figure out the status for each of your names at the point in time identified by the date slicer.

Re: Customized Date Filtering

v-yanjiang-msft — Wed, 30 Mar 2022 10:15:10 GMT

Hi Anonymous ,

According to your description, here's my solution.

1.Create a Date table, put this new date column in the slicer.

Date = CALENDAR(DATE(2001,1,1),DATE(2020,1,1))

2.Create a measure.

Check = IF ( MAX ( 'data'[name] ) IN SELECTCOLUMNS ( FILTER ( ALL ( 'data' ), 'data'[Status] = -1 ), "Name", 'data'[name] ) && MAXX ( FILTER ( ALL ( 'data' ), 'data'[Status] = -1 && 'data'[name] = MAX ( 'data'[name] ) ), 'data'[date] ) < MAXX ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] ) || MAXX ( FILTER ( ALL ( 'data' ), 'data'[Status] = 1 && 'data'[name] = MAX ( 'data'[name] ) ), 'data'[date] ) > MAXX ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] ), 0, 1 )

Put this measure in the visual filter and let the value is 1, get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.