AND Multiple Selectedvalue

[Anonymous]

Hey! 

I wanted to create  a target based on the selections in a slicer.

Target = IF(

AND(SELECTEDVALUE('Table A'[Names])="Thomas",SELECTEDVALUE('Table A'[Names])="Nick")

||SELECTEDVALUE('Return Delivery'[PartStatus1])="Thomas"

||SELECTEDVALUE('Return Delivery'[PartStatus1])="Nick",1,2)

The target if I select only Thomas is 1 like expected. The same goes for Nick. But I want the target to be 1, when I select Thomas AND Nick. So I guess there is something wrong with my underlined part.

All help is very appreciated! 

Best regards

MSOMME

[Anonymous · Accepted solution]

Hey daxer,

thank you very much for your answer. I may be to far away from DAX-Code like in the video, but I somehow figured my own Workaround. It sure is not perfect, but works for me:

Target = IF(

IF(ISFILTERED('Table A'[Names]),CONCATENATEX(VALUES('Table A'[Names]),'Table A'[Names], " | "),"")="Thomas | Nick"

||SELECTEDVALUE('Table A'[Names])="Thomas"

||SELECTEDVALUE('Table A'[Names])="Nick"

,1,2)

Best regards

MSOMME

[Anonymous]

Of course it's wrong. The logic says: if the same field selected in the table is at the same time Nick and Thomas... then do this. Well, this will never be true since SELECTEDVALUE always returns only one name, never both.

To do what you're asking for requires some non-trival effort. Please start with this vid: (1) Using OR conditions between slicers in DAX - YouTube

[Anonymous · Accepted solution]

Hey daxer,

thank you very much for your answer. I may be to far away from DAX-Code like in the video, but I somehow figured my own Workaround. It sure is not perfect, but works for me:

Target = IF(

IF(ISFILTERED('Table A'[Names]),CONCATENATEX(VALUES('Table A'[Names]),'Table A'[Names], " | "),"")="Thomas | Nick"

||SELECTEDVALUE('Table A'[Names])="Thomas"

||SELECTEDVALUE('Table A'[Names])="Nick"

,1,2)

Best regards

MSOMME

[Anonymous · Accepted solution]

Target =
VAR VisibleNames =
    VALUES( 'Table A'[Names] )
var DesiredNames = 
	{"Nick", "Thomas"}
var Union_ =
	// This union will have 2 elements
	// only when VisibleNames is a subset
	// of DesiredSet.
	DISTINCT(
		union(
			VisibleNames,
			DesiredNames
		)
	)
var DesiredNamesCard =
	COUNTROWS( DesiredNames )
var UnionCard =
	COUNTROWS( Union_ )
VAR Result =
    IF( UnionCard = DesiredNamesCard,
        1,
        2
    )
RETURN
    Result

Here's the code you probably wanted to write. CONCATENATEX is a function which should not be used in your scenario. This is not what it's been meant for. The above code is flexible and expresses clearly your idea. For instance, ff you wanted to put more names in the DesiredNames set, you could do it and the code would behave correctly as well (only one change is needed to DesiredNames). If you wanted to change the names, you'd change them in one place only. This is what's called: CLEAN CODE. No repetitions are present and you probably know that in programming repeated code is the source of all evil.

[Anonymous]

Hey daxer,

this is alot better. Thank you very much! 🙂

Best regards 

MSOMME