Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to write Dax condition when selected slicer (multiple selection) is equal values in column

Hi experts,

I need advise and help in Dax writing.

 

Here are the scenario, I have a Slicer with settings multiple selection and value {"A", "B", "C", "D" & "E"}. Next I have a table with column value {"A", "B" & "C"}. Since is multiple selection in slicer means I will have different combination happen for example: selected value is "A", "B" / "B", "C", "E"/ etc.

Since "SELECTEDVALUE" Dax is only able return a value and I found this Dax function [CONCATENATEX(VALUES(Table[Column]),[Column],",")] also will return a value by adding "," between values selected.

 

How can I write Dax condition when selected value is exist in my table column? Below are all scenarios I wish to achieve:
1. "A" & "B" is selected and these value is exist in table column, then return "YES".

2. "D" & "E" is selected and these value is not exist in table column, then retun "NO".
3. "A", "C" & "E" is selected and "E" is not exist in table column, although "A" & "C" is matched, still return "NO"

 

My current method is to get all combination will occured by using this Dax function [CONCATENATEX(VALUES(Table[Column]),[Column],",")] to proceed for my Conditional function, however this seems like very hardcoding and is not best practice.
Sample of my current Dax IF(CONCATENATEX(VALUES(Table[Column]),[Column],",") IN {"A","B","C","A,B","A,C","B,C","A,B,C"}, "YES", "NO).

Kindly advice how the Dax function should write to achieve the same features. Thanks in advance.

 

Best Regards,

Yit Chuen

  • Anonymous , Try mesure like

     


    measure =
    var _cnt = countx(allselected(Slicer), slicer[value])
    return
    if( calculate(distinctCOUNT(Table[column]), filter(Table, Table[column] in values(slicer[value]))) >=__cnt, "Yes", "No")

1 Reply

  • Anonymous , Try mesure like

     


    measure =
    var _cnt = countx(allselected(Slicer), slicer[value])
    return
    if( calculate(distinctCOUNT(Table[column]), filter(Table, Table[column] in values(slicer[value]))) >=__cnt, "Yes", "No")