topic filter multiple columns dynamically in DAX Commands and Tips

filter multiple columns dynamically

RaviKumarG — Mon, 09 Oct 2023 11:41:52 GMT

Hi Team,

I want to filter the multiple columns based on the selectedSlicer Values(having column names (col1, col2, etc).
if I selected both col1 and col2, it has to filter col1>0 && col2>0, return count both condition count
simillarly, if I select 3 items in filter, It has to filter col1>0 && col2>0 && col2>0, return count selectedvalues condition count

COL1 COL2
1 33
3 0
0 55
5 8
0 9

With above data, if I select 2 columns, the desired output is 2- ie (1,33) , (5,8)

Thanks in Advance.

Re: filter multiple columns dynamically

Anonymous — Wed, 11 Oct 2023 02:58:48 GMT

Hi RaviKumarG ,

Please try to create a mesure with below dax formula:

Measure = VAR tmp = { "COL1", "COL2" } VAR col_name = SELECTEDVALUE ( 'Table 2'[Column Name] ) VAR _a = SWITCH ( col_name, "COL1", COUNTROWS ( FILTER ( ALL ( 'Table' ), [COL1] > 0 ) ), "COL2", COUNTROWS ( FILTER ( ALL ( 'Table' ), [COL2] > 0 ) ) ) VAR _b = COUNTROWS ( FILTER ( ALL ( 'Table' ), [COL1] > 0 && [COL2] > 0 ) ) VAR _result = SWITCH ( TRUE (), HASONEVALUE ( 'Table 2'[Column Name] ), _a, ISFILTERED ( 'Table 2'[Column Name] ), _b ) RETURN _result

For more details, please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: filter multiple columns dynamically

Dangar332 — Wed, 11 Oct 2023 08:51:24 GMT

hi, RaviKumarG
i want to add something in above code for your desired output

try below

Measure =

var tmp={"COL1","COL2"}

var col_name=SELECTEDVALUE('Table 2'[Column Name])

var _a=SWITCH(col_name,

"COL1", CALCULATE(COUNTROWS('Table'),'Table'[COL1]>0,KEEPFILTERS('Table'[COL2]>0)) ,

"COL2", CALCULATE(COUNTROWS('Table'),'Table'[COL2]>0,KEEPFILTERS('Table'[COL1]>0)))

var _b=COUNTROWS(FILTER(ALL('Table'),[COL1]>0&&[COL2]>0))

var _result=SWITCH(TRUE(),

HASONEVALUE('Table 2'[Column Name]),_a,

ISFILTERED('Table 2'[Column Name]),_b)

return

_result

Re: filter multiple columns dynamically

RaviKumarG — Wed, 25 Oct 2023 12:10:40 GMT

Thank you Team,

Its working for 2 columns, but I am having more than 30 columns. User may select randomly 2 or 3 or 4 or 5 column names at a time from the slicer.

Requesting you please provide solution that meets the count of columns having greater than 0.

Thanks in Advance.

Re: filter multiple columns dynamically

RaviKumarG — Wed, 25 Oct 2023 12:13:42 GMT

I am using Direct Query having million records.

Thanks