How to create a slicer with levels of criteria

[dkernen]

I would like to create a slicer that allows me to select all records, meets one criteria, and meets both criteria.

I have one field that has 1/0 values that defines "Good" as an example.

I have another field that also has 1/0 values that defines "Excellent."

I want the ability to have a slicer that allows me to select All, Good, and Excellent.  Every record that is Excellent (=1) also has the Good (=1) criteria.  I have no interest in selecting anyone who is not good and I don't need to select anyone who is good, but not Excellent.  When I select All, I don't care if they are good or not.  When I select Good, I don't care if they are also flagged as Excellent.  

Say I have 100 records, 40 of them are good, and 15 of those are both good and Excellent.  So I want the slicer to allow me to choose the 100 OR the 40 OR the 15.  I do not need the residuals.  The Good are a subset of All and the Excellent are a subset of the Good.

Do I create a hierarchy?  Do I have to create a new column?   I am not sure how to solve this.  I appreciate any insight you may be able to share.

@slicer, @hierarchy

[MFelix · Accepted solution]

@dkernen ,

Create a unrelated table with:

SlicerID

All	1
Good	2
Excellent	3

Now add the following measure:

Slicer_Filter =
SWITCH (
    SELECTEDVALUE ( Slicer[ID] ),
    1, 1,
    2,
        CALCULATE (
            MAX ( 'Dimension'[Excellent] ),
            FILTER ( 'Dimension', 'Dimension'[Excellent] = 1 )
        ),
    3,
        CALCULATE (
            MAX ( 'Dimension'[Good] ),
            FILTER ( 'Dimension', 'Dimension'[Good] = 1 )
        )
)

Now use this to filter out your table making the value greater than 0.

Check PBIX file attach.

[MFelix]

Hi @dkernen ,

If the cruiteria level is on column you just need to place that column in a slicer and then uzse it as your slicer.

can you please share a small sample of your data? especcially the items that are not within your criterias.

[dkernen]

Sure!  

DIMENSION

CaseID	Good	Excellent
A	1	0
B	1	1
C	0	0
D	0	0
E	0	0
F	0	0
G	0	0
H	1	1
I	1	0
J	1	1
K	0	0
L	0	0
M	1	0
N	0	0
O	1	1
P	0	0

FACT

CaseID	Amount
A	10
A	12
B	5
B	6
C	7
C	8
D	12
D	45
E	50
E	12
F	43
F	15
F	15
G	1
G	7
G	8
H	25
H	20
H	45
I	13
I	150
J	12
J	4
K	8
K	7
L	14
M	16
N	18
O	20
P	21

[MFelix · Accepted solution]

@dkernen ,

Create a unrelated table with:

SlicerID

All	1
Good	2
Excellent	3

Now add the following measure:

Slicer_Filter =
SWITCH (
    SELECTEDVALUE ( Slicer[ID] ),
    1, 1,
    2,
        CALCULATE (
            MAX ( 'Dimension'[Excellent] ),
            FILTER ( 'Dimension', 'Dimension'[Excellent] = 1 )
        ),
    3,
        CALCULATE (
            MAX ( 'Dimension'[Good] ),
            FILTER ( 'Dimension', 'Dimension'[Good] = 1 )
        )
)

Now use this to filter out your table making the value greater than 0.

Check PBIX file attach.

[dkernen]

 I see how yours works beautifully.  Thank you.  

Originally, my slicer didn't do anything at all.  Then I realized that my key needs to come from the dimension not the fact table.  I also see now that I need to have the fields used for the slicer somewhere in each visual which references (tooltip, additional columns in the matrix, etc)

Thank you for your help.

[MFelix]

Hi @dkernen

You just need to place the measure on the filters pane of the visualization and filter it to above 0 no need to add it to the visualization itself so the additional data is not seen. 

[dkernen]

@MFelix 

Thank you for your help.  I still don't understand why the disconnected table works on some visuals but not others.

https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/EaW79dQSTjxGuWMVZJ_-pkcBTWKG8jAhNwp_w...  On the Duplicate tab, I have the new slicer.  The right two visuals adjust based on the slicer, but the left two do not. 

Any other words of wisdom?  You've been tremendously helpful!

[MFelix · Accepted solution]

Hi @dkernen ,

Measures are based in context, so in your case what is happening is that the two visuals on the left are not related with the context of the measure you are using as filter,

The measure refers to the table 'dimODisp' however both of your visualizations do not relate directly with this table. The calculation of the sum need to be redone accordingly to the selection so you would need to redo the cases to:

SUM_CASES_V2 = 
SWITCH (
    SELECTEDVALUE ( 'MTN Organ Subset'[ID] ),
    1,  SUM(factCase[AnyCase]),
    2,
        CALCULATE (
            SUM(factCase[AnyCase]),
            FILTER (SUMMARIZE(factCase,factCase[Referral_ID], dimODisp[OMTNImminentDeath_N]) , 'dimODisp'[OMTNImminentDeath_N] = 1 )
        ),
    3,
        CALCULATE (
            SUM(factCase[AnyCase]),
            FILTER (SUMMARIZE(factCase,factCase[Referral_ID], dimODisp[OMTNConvDenom_N]) ,  'dimODisp'[OMTNConvDenom_N] = 1 )
        )
)

Has you can see on the Facility short name now the calculation changes with the selection of the slicer. Be aware that I have made the calculation but not sure if this is what you need, please check the numbers.

For the 1st visualization (not yet changed you need to probably replace the CASES measure by the one I have calculated above.

[dkernen]

Stunning.  Wow.  @MFelix  is my Superhero!   Thank you thank you thank you!!!!