Return value based on multiple slicer condition

[dokat]

Hi,

I have 3 slicers in a report

• Customer Slicer
• Customer Tier
• Customer Period

I have below have formula i'd like it to return value if there is any selection in Customer slicer and "Last Year" selection in Customer Period slicer and no selection in Customer Tier slicer. However it is not working. Is this something that can be done with dax ?

OIS Target = IF( SELECTEDVALUE( 'P&L'[Customer] ) = "Tier 4" || SELECTEDVALUE(  'P&L'[Customer] ) = "INNERWORKINGS - 457181 (P)"|| SELECTEDVALUE(  'P&L'[Customer Tier ] ) = 1|| SELECTEDVALUE(  'P&L'[Customer Tier ] ) = 2|| SELECTEDVALUE(  'P&L'[Customer Tier ] ) = 3
    , BLANK(),
VAR OISTarget = if(SELECTEDVALUE(CY[Slicer])="Last Year",
CALCULATE('P&L'[TY], 'P&L'[Close Out] in { "Target"},'P&L'[P&L] in {"Off Invoice Sales"})*1000,
BLANK())
Return
IF (OISTarget =0, BLANK(),OISTarget))

[TheoC · Accepted solution]

Hi @dokat 

You can create a measure that uses similar logic to what you're using but in a reduced way:

Measure = 

VAR _1 = ISFILTERED ( Table[Slicer1] )
VAR _2 = ISFILTERED ( Table[Slicer2] )
VAR _3 = SELECTEDVALUE ( Table[Slicer1] )
VAR _4 = SELECTEDVALUE ( Table[Slicer2] )
VAR _5 = [Value Measure]

RETURN

IF ( OR ( _1 , _2 ) , _3 & " " & _4 , _5 ) )

Below is an example of some dummy data I used. You can see the two slicers are Driver and Attendance.  I've added them together in the event that someone chooses them.  I've also made the measure return the "value" I want if no selection exists i.e. MAX ( t[RANK] ).

Hope this helps!

Theo 🙂

[TheoC · Accepted solution]

Hi @dokat 

You can create a measure that uses similar logic to what you're using but in a reduced way:

Measure = 

VAR _1 = ISFILTERED ( Table[Slicer1] )
VAR _2 = ISFILTERED ( Table[Slicer2] )
VAR _3 = SELECTEDVALUE ( Table[Slicer1] )
VAR _4 = SELECTEDVALUE ( Table[Slicer2] )
VAR _5 = [Value Measure]

RETURN

IF ( OR ( _1 , _2 ) , _3 & " " & _4 , _5 ) )

Below is an example of some dummy data I used. You can see the two slicers are Driver and Attendance.  I've added them together in the event that someone chooses them.  I've also made the measure return the "value" I want if no selection exists i.e. MAX ( t[RANK] ).

Hope this helps!

Theo 🙂

[dokat]

I made few tweaks and the code works, and returns slicer selections. Thank you However i'd like it to return corresponding dollar value for this selection. Return sum(sales)  based on this selection. How can i do that?

[TheoC]

@dokat  Change the _6 section of the measure.

Measure = 

VAR _1 = ISFILTERED ( Table[Slicer1] )
VAR _2 = ISFILTERED ( Table[Slicer2] )
VAR _3 = SELECTEDVALUE ( Table[Slicer1] )
VAR _4 = SELECTEDVALUE ( Table[Slicer2] )
VAR _5 = [Value Measure] // This is what you want to show when no slicer is selected. This can be anything (field, measure, words like "CLICK SOMETHING ALREADY!").
VAR _6 = [Value Measure] // This is what you want to show when selected. This can be a measure or you can use SUM ( Table[Column] ) or whatever else you want.

RETURN

IF ( OR ( _1 , _2 ) , _6 , _5 ) )

[dokat]

Hi Modified cthe code as below but now i am getting "The syntax for ',' is incorrect. (DAX(VAR" error.

VAR Cust = ISFILTERED ('P&L'[Customer])
VAR Period = ISFILTERED ('CY'[Slicer])

VAR Custt = SELECTEDVALUE ('P&L'[Customer])
VAR Periodd = SELECTEDVALUE ('CY'[Slicer])
VAR BLAN = BLANK()
VAR OISTarget= CALCULATE('P&L'[TY], 'P&L'[Close Out] in { "Target"},'P&L'[P&L] in {"Off Invoice Sales"})*1000

RETURN
IF ( OR ( Cust , Period ) , BLANK, OISTarget )

[dokat]

@TheoC  Thank you for your response. I also want this code to work only when "Last Year" is selected in Period Slicer and there is no selection in Customer Tier slicer and any selection made or not made in Customer slicer.

I modified code  however it is returning blank value. Is there anyway to fix this?

VAR Cust = ISFILTERED ('P&L'[Customer])
VAR Period = ISFILTERED ('CY'[Slicer])

VAR Custt = SELECTEDVALUE ('P&L'[Customer])
VAR Periodd = SELECTEDVALUE ('CY'[Slicer])
VAR BLAN = BLANK()
VAR OISTarget= CALCULATE('P&L'[TY], 'P&L'[Close Out] in { "Target"},'P&L'[P&L] in {"Off Invoice Sales"})*1000

RETURN
IF ( OR ( Cust , Period ) , BLAN, OISTarget )

[TheoC]

@dokat in the future, can I please ask that you incorporate all the things you require in your original post. It makes it very challenging for a person who is searching for a solution to identify exactly what the solution is compared to the original issue at hand.

In terms of your measure, it's returning BLANK because you are telling the measure to return a BLANK() if / or the P&L Customer or CY Slicer has anything selected.

[TheoC · Accepted solution]

@dokat can I ask that you please accept the original solution as it meets your initial post.  I can continue responding to any of your questions, however, want to ensure the initial challenge is resolved so that others can search.  Thanks, Theo

[dokat]

Yes i accepted as solution. When i change the order of the  var 5 and 6 but it's still returning Blank,

Is there a way to return value when 'CY'(Slicer] is equal to "Last Year", there is no selection made for Customer Tier slicer and any selection made or not made in Customer slicer?

Thanks

[TheoC]

Hi @dokat thanks for that.

Can you send me the exact measure - the whole thing - that you currently have in Power BI?