Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

AND Multiple Selectedvalue

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's avatar
    Anonymous
    5 years ago

    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's avatar
    Anonymous
    5 years ago
    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago
        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.