topic Re: AND Multiple Selectedvalue in DAX Commands and Tips

AND Multiple Selectedvalue

Anonymous — Wed, 28 Jul 2021 09:00:39 GMT

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

Re: AND Multiple Selectedvalue

Anonymous — Wed, 28 Jul 2021 09:58:22 GMT

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

Re: AND Multiple Selectedvalue

Anonymous — Wed, 28 Jul 2021 11:30:04 GMT

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

Re: AND Multiple Selectedvalue

Anonymous — Wed, 28 Jul 2021 11:54:05 GMT

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.

Re: AND Multiple Selectedvalue

Anonymous — Wed, 28 Jul 2021 12:00:23 GMT

Hey daxer,

this is alot better. Thank you very much! 🙂

Best regards

MSOMME