topic Re: Multiple Select option in DAX Commands and Tips

Multiple Select option

ShivGC — Thu, 19 Oct 2023 10:02:55 GMT

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 🙂

Re: Multiple Select option

lbendlin — Sat, 21 Oct 2023 20:22:35 GMT

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

Re: Multiple Select option

Anonymous — Mon, 23 Oct 2023 02:30:14 GMT

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.

Re: Multiple Select option

ShivGC — Mon, 23 Oct 2023 14:30:54 GMT

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?

Re: Multiple Select option

lbendlin — Mon, 23 Oct 2023 14:40:32 GMT

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

For that you need to use VALUES()

Re: Multiple Select option

Anonymous — Tue, 24 Oct 2023 07:54:33 GMT

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.

Re: Multiple Select option

ShivGC — Tue, 24 Oct 2023 09:29:08 GMT

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

Here is a code that worked for me 🙂