how to show productnames in the x-axis based on selected flag slicer

[Powers]

Hi Experts,

I am facing a challenge in one of the scenario, the scenario is I have a isBeverage slicer and it has 2 values 0 and 1. If user selects 0 then data should show totals for 0 and in the x-axis it should show selectedvalue(0) respective productnames in the x-axis. Similarly, If user selects 1 then data should show totals for 0 and 1 productnames and similarly in the x-axis it should both 0 value products and selectedvalue (1) product values. I have created a measure and I am able to get the values and x-axis values for selectedvalue =0 but when I select 1 then I am not getting expected result.

Below is my sample dataset

Productname	Sales Amount	IsBeverage
Car	8151054	0
Bi-Cycle	359426	0
Bike	559930	0
Ship	483167	0
Bus	53351	0
Cool drinks	963552	1
Cookies	750092	1
Chocolates	536857	1
Chips	85937	1

Requirement:
I have IsBeverage slicer in the report and have clustered column chart in the report. In the below chart, I have selected 0 and the chart is getting filtered based on the selected value and it's showing productnames based on selected value.

But where as in the 2nd chart, If select 1 then I want to get both 0 and 1 productnames in the x-axis, which I am not able get it.

My required output is, If I select 1 then both 0 and 1 productnames should display in the x-axis and it's values. Please see the below expected chart.

Below is the measure which I have created for this chart

Isproduct = IF(SELECTEDVALUE(Sheet1[IsBeverage])=1,CALCULATE(SUM(Sheet1[Sales Amount]),Sheet1[IsBeverage] IN {0,1}),IF(SELECTEDVALUE(Sheet1[IsBeverage])=0,CALCULATE(SUM(Sheet1[Sales Amount]))))

It would be a great help, If anyone can help on this issue.

Best Regards,
POWERBI

[Greg_Deckler]

@Powers You need to use ALL in order to override the filtering of the slicer so something like:

Isproduct = IF(SELECTEDVALUE(Sheet1[IsBeverage])=1,CALCULATE(SUM(Sheet1[Sales Amount]),FILTER( ALL( Sheet1), [IsBeverage] IN {0,1}) ,IF(SELECTEDVALUE(Sheet1[IsBeverage])=0,CALCULATE(SUM(Sheet1[Sales Amount]))))

[Powers]

Hi @Greg_Deckler,

Thanks for the response. I have used your measure but still not getting 0 and 1 beverage products in the x-axis, instead it's only showing beverage 1 prouducts in the x-axis.

Best Regards,
POWERBI

[Greg_Deckler · Accepted solution]

@Powers OK, use a disconnected table then. PBIX is attached and use a measure like this:

Measure = 
    VAR __Slicer = SELECTEDVALUE( 'Slicer'[IsBeverage] )
    VAR __IsBeverage = MAX( 'Table'[IsBeverage] )
    VAR __Sum = SUM( 'Table'[Sales Amount] )
    VAR __Result = 
        SWITCH( __Slicer, 
            1, __Sum,
            0, IF( __IsBeverage = 0, __Sum )
        )
RETURN
    __Result

[Powers]

@Greg_Deckler , Thanks for the solution. It's working as expected and I have changed few logics in the measure as per my scenario but I solved this problem based your inputs. Thanks a lot.

[Syk]

Under your x-axis:

[Powers]

@Syk , Thanks for the response. But that's not working.