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

[don_writer]

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 🙂

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:

ID	Date_Separation	Reason
1	8/31/2018	Involuntary - Death
2	9/1/2018	Involuntary - Retirment
3	9/2/2018	Involuntary - Other
4	9/3/2018	Voluntary - Retirement
5	9/4/2018	Voluntary - Other
6	9/5/2018	Involuntary - Other
7	9/6/2018	Voluntary - Retirement
8	9/7/2018	Voluntary - Retirement
9	9/8/2018	Voluntary - Retirement
10	9/9/2018	Involuntary - Death
11	9/10/2018	Involuntary - Other
12	9/11/2018	Voluntary - Retirement
13	9/12/2018	Involuntary - Other
14	9/13/2018	Involuntary - Other
15	9/14/2018	Voluntary - Retirement
16	9/15/2018	Voluntary - Retirement
17	9/16/2018	Involuntary - Retirment
18	9/17/2018	Involuntary - Retirment
19	9/18/2018	Voluntary - Retirement
20	9/19/2018	Involuntary - Other
21	9/20/2018	Involuntary - Other
22	9/21/2018	Involuntary - Retirment
23	9/22/2018	Involuntary - Other
24	9/23/2018	Voluntary - Other
25	9/24/2018	Involuntary - Other
26	9/25/2018	Voluntary - Other
27	9/26/2018	Involuntary - Retirment
28	9/27/2018	Involuntary - Other
29	9/28/2018	Involuntary - Retirment
30	9/29/2018	Voluntary - Other
31	9/30/2018	Involuntary - Other
32	10/1/2018	Involuntary - Other
33	10/2/2018	Involuntary - 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

[LaurentCouartou]

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]

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

[LaurentCouartou]

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).