topic Multiple selection filters not working in DAX Commands and Tips

Multiple selection filters not working

Velvetine27 — Mon, 09 Jan 2023 01:58:36 GMT

Hi all,

I wrote a DAX to display data up till the selected month like this :

%CFR=

VAR selectedYear =

SELECTEDVALUE ( 'DATE'[Date].[Year] )

VAR selectedMon =

SELECTEDVALUE ( 'DATE'[Date].[MonthNo] )

VAR start_ =

DATE ( selectedYear, 1, 1 )

VAR end_ =

DATE ( selectedYear, selectedMon + 1, 1 ) - 1

RETURN

CALCULATE(([DO] / [SO]),FILTER ( 'TABLE', 'TABLE'[date] >= start_ && 'TABLE'[date] <= end_ ))

Now the issue is that in my visuals if the user selects more than one month, this doesn't work. Do you guys have any suggestions how can I keep the functionality of current DAX while allowing users to select multiple months and it will show data up till the last selected month. So if users choose all months, it should display the entire year of data. Any suggestions appreciated.

Thanks !

Re: Multiple selection filters not working

FreemanZ — Mon, 09 Jan 2023 02:23:05 GMT

hi Velvetine27

try like:

%CFR=

VAR selectedYear =

YEAR(MAX('DATE'[Date]))

VAR selectedMon =

MONTH(MAX('DATE'[Date]))

VAR start_ =

DATE ( selectedYear, 1, 1 )

VAR end_ =

DATE ( selectedYear, selectedMon + 1, 1 ) - 1

RETURN

CALCULATE(([DO] / [SO]),FILTER ( 'TABLE', 'TABLE'[date] >= start_ && 'TABLE'[date] <= end_ ))

Re: Multiple selection filters not working

Anonymous — Tue, 10 Jan 2023 07:49:51 GMT

Hi Velvetine27 ,

I created some data:

Table:

Date:

Here are the steps you can follow：

1. Create measure.

Flag = var _selectyear=SELECTEDVALUE('Date'[Year]) var _selectmonth=MAXX(ALLSELECTED('Date'),[Month]) var _start=DATE(_selectyear,1,1) var _end=EOMONTH(DATE(_selectyear,_selectmonth,1),0) return IF( MAX('Table'[date]) >=_start && MAX('Table'[date])<=_end,1,0)

Measure = SUMX(ALLSELECTED('Table'),[DO/SO])

2. Result:

Best Regards,

Liu Yang

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