3 slicer selection to show different measure with option to show Both

[cruzp]

Hello Team, 

I have a chart below:

Is it possible to have a slicer named "Closed Lost"?

That slicer will have 3 selections such as:

Both - to show both Closed Lost and Closed Won (both will appear standing next to each other)
Yes - to show only Closed Lost (red bar)
No - to show only Closed Won (green bar)

Is that something doable?

[danextian · Accepted solution]

Hi @cruzp 

You can achieve this by using a disconnected table that holds the slicer values and a measure to return the slicer selection.

Sum of Value = 
VAR _SLICER =
    SELECTEDVALUE ( Slicer[Slicer], "Both" )
RETURN
    SWITCH (
        _SLICER,
        "Both", SUM ( Data[Value] ),
        "Win", CALCULATE ( SUM ( Data[Value] ), KEEPFILTERS ( Data[Category] = "Win" ) ),
        "Loss", CALCULATE ( SUM ( Data[Value] ), KEEPFILTERS ( Data[Category] = "Loss" ) )
    )

Please see attached pbix for details.

[cruzp]

Hi @danextian , we are almost there! just a quick question, why am i getting blank when 'Both' is selected as filter? the blank is the value without the condition (won/lost) 

i wonder if its because the stage_name is coming from a different table

[danextian]

Both categories are supposed to show  when both is selected from the salaries and not an extra column showing the sum of both categories. Did you use the category column from your fact table as the legend or the slicer's? There also shouldn't be a relationship between slicer and fact the slicer being a disconnected table. Please check again on my sample  file. I can't think of anything else other than that as there may be other factors in your model that is causing this.

[cruzp]

@danextian i think because the category i am using is a filtered column from power query, so it naturally shows blank. I can just exclude the blank and it would solve the issue. THANK YOU A LOT!

[mostho]

1. if closed won and closed lost are columns, define two measure (sum('closed lost') and sum('closed won') - if they are already measures you can skip this step.
2. define a field parameter with the two measures:

Parameter = {
    ("won", NAMEOF('Data'[msWon]), 0),
    ("lost", NAMEOF('Data'[msClosed]), 1)
}

3. use this parameter in the graph and in the slicer:

[cruzp]

Hi @mostho , 

Showing errors. 

[mostho]

> Showing errors.

Here is my working minimal example.

[cruzp]

@mostho ,

not showing properly.

[mostho]

You do not use the parameter in the graph as y-axis. look at my example to understand this

[cruzp]

@mostho hi, you are actually using it in column y-axis and it works fine in your report. however, if i apply it in my report, it does not show same.

your report:

mine:

i create the parameter table by simply "New Table"

[mostho]

i said define a field parameter with the two measures, not a table.

[cruzp]

@mostho it worked, thank you. is there a way to customize colors like for example if i select Won, the color would be Green, if Lost is selected, then it would be Red. Is that doable?

[mostho]

yes, it is:



You can set color and transparency per column.

[cruzp]

@mostho is there a way to prevent showing one color if only one measure is selected in the slilcer?

the colors works when both measure in sllicer is selected, however if you select either one of them, it shows similar color, and you only have "All" option in the formatting color.

[mostho]

it seems to be an issue with column charts, if there is only one column selected then this setting dialog switches to a-"category"-settings dialog. with a line chart for example the colors stay the same (in my observation).

[cruzp]

Does that mean I cant change the color when one is selected?

[mostho]

Try it.

If only one field in the slicer is selected, the settings changes to a category-setting:

[mostho]

> Showing errors.

Well just posting a screenshot with an error message and not saying what you have done and how your data model looks like makes it really hard to help.

Anyway: here is my minimal working example, maybe you can see the difference to yours.