Forum Discussion

Joe_Reed's avatar
Joe_Reed
Frequent Visitor
5 years ago

Question about configuring multiple slicers and multiple DAX Switch statements

To all;

 

 I have a real world use case where I'm asked to take multiple slicers, set up as toggles, i.e.

 - all data is numeric, could be expressed as text for display  (not actual DAX code)

slicers:

Average/Total    "Average" = DIVIDE(<data> ,SUM(weeks))   

                           "Total "= SUM(<data>)

 

Dollars/Units  "Dollars" = SUM(<data>)         

                       "Units"    = SUM(<data>)

 

 CPIU/ROI       "CPIU" = ( return - cost) / units

                        "ROI"  = ( Return - cost)

 

         The basic toggles work well, independently, but my use case needs them to 'nest' or 'cascade'

the slicer slections and related value measures, so that data ex. "Spend" could be filtered by *Both*

Dollars/Units and then Average/Total

           

  The data model is enormous, and I have little control over import of data not included in the original

model.

 

  Any advice, suggestions or reasonably effective magic spells would be deeply appreciated

        Thank You !

             Joe

Multiple DAX switches and multiple slicers

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    5 years ago

    I'm not quite sure I understand the situation. How do you have your measures written so that they work independently but not combined?

  • Joe_Reed's avatar
    Joe_Reed
    Frequent Visitor
    5 years ago

    these are some of the mesauses being toggled 

    Examples:

    ------------------------------------

    Toggle Dollars/Units

    Base Dollars/Units =
    IF (
    ISCROSSFILTERED (DollarsUnits[DollarsUnits]),
    SWITCH ( TRUE(),
     
    VALUES(DollarsUnits[DollarsUnits])= "Dollars",Format('Promos'[SwitchBaseDollars],"$#,##"),
    VALUES(DollarsUnits[DollarsUnits])= "Units",'Promos'[SwitchBaseUnits],
    -------------------------------------------------------------
    SwitchBaseDollars = DIVIDE(SUM(Promos[Base Dollars]),sum('Promos'[Promo Weeks]))
    SwitchBaseUnits = DIVIDE(SUM(Promos[Base Units]),sum('Promos'[Promo Weeks]))
     

    BLANK()),BLANK())
     
  • lance_6's avatar
    lance_6
    Icon for Helper II rankHelper II
    5 years ago

    This is just me theorizing. You probably have a slicer table to select the correct measures:

     

    | Slicer Measure | Slicer Value |

    | Average/Total  | 1                 |

    | Dollars/Units    | 2                 |

     

    and then some measures that look something like:

     

     

    AverageSlicer = 
IF(
 SELECTEDVALUE( 
  SlicerTable[Slicer Value] = 1 
  || ISBLANK(SELECTEDVALUE(SlicerTable[Slicer Value])
 ),
 [Average]
)

TotalSlicer = 
IF(
 SELECTEDVALUE( 
  SlicerTable[Slicer Value] = 2 
  || ISBLANK(SELECTEDVALUE(SlicerTable[Slicer Value])
 ),
 [Total]
)

     

     

    I propose something like this:

    | Objective Slicer | Slicer Measure | Slicer Value |

    | Average/Total  | Average/Total  | 1                 |

    | Dollars/Units    | Dollars/Units    | 2                 |

    | Both                 | Average/Total  | 3                 |

    | Both                 | Dollars/Units    | 3                 |

     

    AverageSlicer = 
IF(
 SELECTEDVALUE( 
  SlicerTable[Slicer Value] = 1 
  || SlicerTable[Slicer Value] = 3 
  || ISBLANK(SELECTEDVALUE(SlicerTable[Slicer Value])
 ),
 [Average]
)

TotalSlicer = 
IF(
 SELECTEDVALUE( 
  SlicerTable[Slicer Value] = 1 
  || SlicerTable[Slicer Value] = 3 
  || ISBLANK(SELECTEDVALUE(SlicerTable[Slicer Value])
 ),
 [Total]
)