cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
don_writer Regular Visitor
Regular Visitor

DAX - Multivariate Slicers or whatever the kids are calling it these days

Hi gurus,

 

I am certain this is a thing but I wasn't certain what I would search under. If it is a thing please link the solution and I can possibly rename the title of the ask. If its not a thing, (and I'd be shocked) maybe I just named it Smiley Happy

 

Anyway, onto my thing...

 

I have a separation table with ID, date_separation, reason

  • The reason comes in five flavors: 
    Involuntary - Death
  • Involuntary - Retirement
  • Involuntary - Other
  • Voluntray - Retirement
  • Voluntary - Other

 

My stakeholders was a slicer that selects out:

  • Death
  • Involuntary Excluding Retirement (everything but retirement)
  • All Intoluntary together
  • Volumtary excluding Retirement
  • All Voluntary together

 

So if I had a table that looked like this:

 

IDDate_SeparationReason
18/31/2018Involuntary - Death
29/1/2018Involuntary - Retirment
39/2/2018Involuntary - Other
49/3/2018Voluntary - Retirement
59/4/2018Voluntary - Other
69/5/2018Involuntary - Other
79/6/2018Voluntary - Retirement
89/7/2018Voluntary - Retirement
99/8/2018Voluntary - Retirement
109/9/2018Involuntary - Death
119/10/2018Involuntary - Other
129/11/2018Voluntary - Retirement
139/12/2018Involuntary - Other
149/13/2018Involuntary - Other
159/14/2018Voluntary - Retirement
169/15/2018Voluntary - Retirement
179/16/2018Involuntary - Retirment
189/17/2018Involuntary - Retirment
199/18/2018Voluntary - Retirement
209/19/2018Involuntary - Other
219/20/2018Involuntary - Other
229/21/2018Involuntary - Retirment
239/22/2018Involuntary - Other
249/23/2018Voluntary - Other
259/24/2018Involuntary - Other
269/25/2018Voluntary - Other
279/26/2018Involuntary - Retirment
289/27/2018Involuntary - Other
299/28/2018Involuntary - Retirment
309/29/2018Voluntary - Other
319/30/2018Involuntary - Other
3210/1/2018Involuntary - Other
3310/2/2018Involuntary - Retirment

 

 And I selected one of the below slicers I'd get the number of records shown here:

  • Death :  1 in August, 1 in September, none in October
  • Involuntary Excluding Retirement (everything but retirement) : none in August, 6 in September, 1 in October
  • All Involuntary together : 1 in August, 18 in September, 2 in October
  • Voluntary excluding Retirement : none in August, 4 in September, none in October
  • All Voluntary together : none in August, 12 in September, none in October

 

As the title suggests I'd like to use DAX. I'm certain it has something to do with maybe a switch and/or If then statement on a New Column but since two slicer options have to include another slicer option I am uncertain how I'd go about this (and I have been staring at it too long.)

 

I'd love to hear your thoughts. Thank you.

~Don

 

 

3 REPLIES 3

Re: DAX - Multivariate Slicers or whatever the kids are calling it these days

Is this what you are looking for ?
https://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/

 

However, you may also consider the use of bookmarks and filters.

don_writer Regular Visitor
Regular Visitor

Re: DAX - Multivariate Slicers or whatever the kids are calling it these days

Thanks for your input.

 

I don't think this would be a selection of measures (but I could be wrong). I've used dynamic measures before and I can't think how that would work in this scenario. (and I don't want separate buttons, rather a drop-down).

 

With the measures what would I do? Create five different fields that identify if requirements are met? Option A, B, C, D, E columns? Then make a table that somehow references those separate columns? I feel like I am missing some crucial part.

 

Edit: Oh, I was also thinking I could somehoe use the SELECTEDVALUE function or something similar but not quite there yet.

 

Best regards,

Don

Re: DAX - Multivariate Slicers or whatever the kids are calling it these days

 

The reason you cannot use a regular slicer/filter here, is because, a row can normally only be associated with one category. In your example, however, "Involuntary - Death" belongs to several categories.

There is no DAX around that, unless you either
1 - build a model that lets you associate one row with several rows in you filter table : 
           https://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/
2 - or add a filter table has no relationship with the table you have to filter and write the DAX that lets you SWITCH between different behaviors: 
          https://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/

 

 

Option 2 is the option you were alluding to, as I understand it. (Option 1, while an obvious fit in this case, also has pitfalls).

 

 

The article I mentioned above shows you how to add a table to your model and then use SWITCH to alter the behavior of a measure depending on your selection (on this table).


Say you add a new table FilterTable to your model (no relationship with anyother table). You can then write a measure that looks  like this :

Display measure = SWITCH( SELECTEDVALUE(FilterTable[Reason category]))
     , BLANK(), BLANK()
     , "Involuntary Excluding Retirement (everything but retirement)"
          , CALCULATE( [Base measure], 
                , MainTable[Reason] IN {{"Involuntary - Death"}, {"Involuntary - Death"}}
	)
     , "All Voluntary together"
          , CALCULATE( [Base measure], 
                , MainTable[Reason] IN {{"Involuntary - Death"}, {"Involuntary - Death"}}
	)
     , ...
)

 

 

Notes:
 I added SELECTEDVALUE in the example, but it is the same idea as in the article I mentioned. 
SELECTEDVALUE returns BLANK when you have no selection (or no value in your selection). This must be tested (first row in the selection).