topic Re: Ignore row filter to get list of dates to selected date in DAX Commands and Tips

Ignore row filter to get list of dates to selected date

Asmoday1507 — Fri, 22 Dec 2023 15:15:48 GMT

Hey everyone

I'm having a huge problem avoiding the row filter when selecting a specific date. For the sake of example, let's assume I have 1 table, called DIM_CALENDAR, and a measure that counts the number of days selected. If I select a specific date on the slicer, I get that one date, and I would like to get all the dates up to that selected date.

The only solution I can see, but is not accepted by the business, is to set the date slicer to a period up to a specific date, but unfortunately the business wants to select dates from a drop-down list.

My measure (not working):

CALCULATE(

COUNT(DIM_CALENDAR[Date]),

FILTER(

KEEPFILTERS(VALUES('DIM_CALENDAR'[Date])),

'DIM_CALENDAR'[Date] <= SELECTEDVALUE(DIM_CALENDAR[Date])

)

But I get only one date (because one date is selected, but I wanna see all dates to this date)

How to write correct measure in dax?

My measure doesn't working:

I wanna see list of dates to this selected date on the right side

Re: Ignore row filter to get list of dates to selected date

VahidDM — Fri, 22 Dec 2023 23:35:48 GMT

Hey Asmoday1507

Try this:

All Dates to Selected = CALCULATE( COUNTROWS('DIM_CALENDAR'), FILTER( ALL('DIM_CALENDAR'), 'DIM_CALENDAR'[Date] <= MAX('DIM_CALENDAR'[Date]) ) )

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

Appreciate your Kudos!!

LinkedIn | Twitter | Blog | YouTube

Re: Ignore row filter to get list of dates to selected date

Anonymous — Mon, 25 Dec 2023 08:03:55 GMT

Hi Asmoday1507 ,

Based on what you’ve mentioned, to get all the dates up to that selected date, you can follow these steps:

Here are my test data:

1.Create a calculate table

Table = VALUES('DIM-CALENDER'[Date])

2.Create a Slicer by using DATE column of table

3.Create another and set to the filter of this visual(table), and set options as ‘is 1’

Measure = VAR Selected_value = SELECTEDVALUE('Table'[Date]) RETURN IF( MAX('DIM-CALENDER'[Date]) <= Selected_value, 1, 0)

4.Final output

In order for you to solve the problem faster, you can refer to the following documentation

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Best Regards,
Albert He