Forum Discussion

ShivGC's avatar
ShivGC
Helper I
2 years ago
Solved

Multiple Select option

Hi 

I couldn't quite find the answer to my question online so I decided to drop the question on the forums myself. 

 

I have two tables that have no relationship

Client (Distinct) Table:

Client

Client 1

Client 2
Client 3

 

Client Information Table:

Client SaleItem name
Client 150

Pencil

Client 160

Sharpner

Client 270Calculator
Client 375Pencil
Client 380Sharpner

 

I created a DAX code (that works) which highlights a selected client one colour and their competitors a different colour which is:

 

Client Distiction = 
var selectedClient = SELECTEDVALUE('Client Distinct Table'[Client])
return 
SWITCH(
 SELECTEDVALUE(Client Information Table[Client])=selectedClient, 
 TRUE, 
 "#006D9E" , 
 "#A6E2EF" 
)

 

 

This code works if you use two different filters and put the client distinct table 'client' coloumn in the first filter, and the client infromation table 'client' column in the second filter. If you select one client in the first filter, then it will highlight that client if selected in the second filter.

However, if you select multiple clients in the first filter it will not longer colour the selected clients in the chart. Is there a way to fix this? So, if you select client 1 and client 2, both these clients will be highlighted in a scatter graph/bar chart/line chart,  and the rest of the clients will be highlighted another colour. 

Thank you for your help. Look forward to the responses. 

I am a beginner so please explain it as simply as possible 🙂

"DAX"
"Help"
"Need Help"
"SELECTEDVALUE"
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ShivGC ,

    Please try to create a measure with below dax formula:

    Measure =
VAR _a =
    SELECTEDVALUE ( 'Client  Information'[Client ] )
VAR tmp =
    SELECTCOLUMNS ( Client, "Client Name", [Client] )
VAR _str =
    CONCATENATEX ( tmp, [Client Name], "," )
VAR _result =
    IF ( CONTAINSSTRING ( _str, _a ), "#006D9E", "#A6E2EF" )
RETURN
    _result

    Add a scatter visual with Client Information table, and set the markers color

     

    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.

  • ShivGC's avatar
    ShivGC
    2 years ago
    IsTable1InTable2 = 
IF(
    COUNTROWS(
        FILTER(
            'Table1',
            'Table1'[ColumnName] IN VALUES('Table2'[ColumnName])
        )
    ) > 0,
    TRUE,
    FALSE
)



    Here is a code that worked for me 🙂

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    You may want to look at the "Decomposition tree"  visual.  It can give you these answers much faster.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ShivGC ,

    Please try to create a measure with below dax formula:

    Measure =
VAR _a =
    SELECTEDVALUE ( 'Client  Information'[Client ] )
VAR tmp =
    SELECTCOLUMNS ( Client, "Client Name", [Client] )
VAR _str =
    CONCATENATEX ( tmp, [Client Name], "," )
VAR _result =
    IF ( CONTAINSSTRING ( _str, _a ), "#006D9E", "#A6E2EF" )
RETURN
    _result

    Add a scatter visual with Client Information table, and set the markers color

     

    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.

    • ShivGC's avatar
      ShivGC
      Helper I
      2 years ago

      Do you know why the Selected value function does not work for multiple select? 

      I wanted to use an alternative of the selected value so I could use it in the calculate function too. 

      I do not undertsand why Selectedvalue will not work when I select more than one value?

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago
        Do you know why the Selected value function does not work for multiple select?

        For that you need to use VALUES()

  • ShivGC's avatar
    ShivGC
    Helper I
    2 years ago
    IsTable1InTable2 = 
IF(
    COUNTROWS(
        FILTER(
            'Table1',
            'Table1'[ColumnName] IN VALUES('Table2'[ColumnName])
        )
    ) > 0,
    TRUE,
    FALSE
)



    Here is a code that worked for me 🙂