Multiple Select option

[ShivGC]

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	Sale	Item name
Client 1	50	Pencil
Client 1	60	Sharpner
Client 2	70	Calculator
Client 3	75	Pencil
Client 3	80	Sharpner

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 🙂

[Anonymous · Accepted solution]

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 · Accepted solution]

IsTable1InTable2 = 
IF(
    COUNTROWS(
        FILTER(
            'Table1',
            'Table1'[ColumnName] IN VALUES('Table2'[ColumnName])
        )
    ) > 0,
    TRUE,
    FALSE
)

Here is a code that worked for me 🙂

[Anonymous · Accepted solution]

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]

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?

[Anonymous]

Hi @ShivGC ,

For Selectedvalue() function, it return the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.

When you select more than one value in slicer, it will return blank when not set the second parameter.

For more details, you can read related document: SELECTEDVALUE function - DAX | Microsoft Learn

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.

[lbendlin]

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

For that you need to use VALUES()

[lbendlin]

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