Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
I am using Direct Query having million records.
Thanks
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.
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.
hi, @RaviKumarG
i want to add something in above code for your desired output
try below
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |