filter multiple columns dynamically

[RaviKumarG]

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.

[RaviKumarG]

I am using Direct Query having million records.

Thanks

[RaviKumarG]

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.

[Anonymous · Accepted solution]

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.

[Dangar332]

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