cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ansa_naz Member
Member

How to use multiple slicer values to filter measure results

Hi all

 

I have been pondering this problem for a while now, and looked at lots of posts online about how to use slicer selection in a measure, but they all deal with ONE selection. I need to deal with multiple selections.

 

I have a disconnected table called Overdue, with below values:

 

Yes

No

No Report

 

I have added this to a slicer. I need to create a measure which does the following:

 

 

 

RepDate = 

VAR _LastDate =
    MAX ( Reports[Report date] )

RETURN

IF(
        // This first IF condition forces the measure to evaluate in the context of the visual
	HASONEVALUE(Lifts[Lift ID]),
                // This next condition avoids getting the error that the visual cannot be displayed
		IF(HASONEVALUE(Overdue[Overdue]),
                        // This switch statement uses the values from Overdue slicer to determine which measure to display
			SWITCH(
          			VALUES(Overdue[Overdue]),
          			"Yes",[RepDateYes],
                                "No",[RepDateNo]
                    ),
            // If the Overdue slicer has not been selected, just display the date of the report
			if(COUNTROWS(Overdue)=3,if(ISBLANK(_LastDate),blank(),_LastDate),

            // If the Overdue slicer has multiple selections, display measures only for slicer selections
            if(CONTAINS(Overdue,Overdue[Overdue],"Yes",Overdue[Overdue],"No"),[RepDateYes]||[RepDateNo]
            )

		    )
            
)
)

Its the fourth IF statement I cannot get to work properly. RepDateNew is used in a table visual, and I want to show values from 2 measures if 2 slicer selections have been made. But all I get is FALSE value in every row when multiple selections are made. See below:

 

Multi slicer selection.jpg

 

Any help would be most appreciated

 

I also have an example PBI report below:

 

https://1drv.ms/u/s!AuiIgc_S9J5JhbYL_-CFbONVzOLWDA

 

Cheers

2 REPLIES 2
Super User
Super User

Re: How to use multiple slicer values to filter measure results

Something does not look correct about that 4th IF

 

if(

  CONTAINS(Overdue,Overdue[Overdue],"Yes",Overdue[Overdue],"No")  //condition

  ,[RepDateYes]||[RepDateNo]  //true

)

 

Where is your false condition? Also, perhaps break your contains into 2 conditions with an || between them.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


ansa_naz Member
Member

Re: How to use multiple slicer values to filter measure results

Hi @Greg_Deckler, thanks for your reply

 

I thought a double pipe || was used as an OR in DAX? I need the condition to read as an AND, so if BOTH those two slicer selections are made then return BOTH the two measures in the TRUE part. Any ideas how I can do that?

 

I have amended the overall measure to read as below so it has a FALSE statement too, althougnh I thought it wasnt needed in DAX? I have read lots of IF statements online where the FALSE part is not always there if it isnt needed?

 

// If the Overdue slicer has multiple selections, display measures only for slicer selections
            if(CONTAINS(Overdue,Overdue[Overdue],"Yes",Overdue[Overdue],"No"),[RepDateYes]||[RepDateNo]
            , if(CONTAINS(Overdue,Overdue[Overdue],"Yes",Overdue[Overdue],"No Report"),[RepDateYes]||[RepDateNoReport]
            ,[RepDateNo]||[RepDateNoReport]
            ))

 

The visual still shows TRUE/FALSE (incorrectly), instead of returning the measure values?

 

Multi slicer selection1.jpg

 

Cheers