How to calculate "All" in single select slicer

[DataScientist]

I have two tables with Browser column. Table A has the following values in Browser column,

{"All", "Chrome", "IE", "Edge", "Firefox", "Safari"}

Table B has the following values in Browser column (the same as table A except "All")

{"Chrome", "IE", "Edge", "Firefox", "Safari"}

I want to use a slicer to display value based on user selection. But due to the values in other columns, table B doesn't allow multiple selection (there is no valid data when selecting both "Chrome" and "IE", for example). So I set the slicer to be single select. But table B doesn't have "All" value. So the question is, is there a way to define a logic to let table B display aggregated data of the rest values when user selects "All" (Chrome + IE + Edge + Firefox + Safari)? Basically is to simulate the logic of "select all" in a single select slicer.

[jdbuchanan71 · Accepted solution]

Hello @DataScientist 

I uploaded my sample .pbix file here BrowserModel.pbix

I created a calculated table to pull together the browser list from both tables (never know when a new one will hit your data)

Browsers = 
DISTINCT(
    UNION(
        DISTINCT(TableA[Browser]),
        DISTINCT(TableB[Browser])
    )
)

Then I join that back into both Table A and Table B.

The calcs for Table A are straight forward and with the browser table the calcs for Table B are not too bad.  I have a date column in my sample so I also have a date table just for testing the behavior.

Table B Measure = 
VAR Browser = SELECTEDVALUE( Browsers[Browser] )
VAR DatesSelected = VALUES ( Dates )
RETURN
IF ( Browser = "All",
    CALCULATE( SUM ( TableB[Users] ), ALL ( TableB ), DatesSelected ),
    SUM ( TableB[Users] )
)

Here is a view from the linked .pbix showing the raw data and filtered visuals output

[DataScientist]

I have a further question, @jdbuchanan71 . There are 4 pivots in my report with this problem that table A has "All" value, but table B doesn't. How can I tweak the logic for table B measure to count for 4 pivots? The total count of possible combinations of selecting "All" in 4 pivots is about 16. Is there an easy way to present it in the measure?

Table B Measure =

VAR Browser = SELECTEDVALUE( Browsers[Browser] )

VAR DatesSelected = VALUES ( Dates )

RETURN

IF ( Browser = "All",

    CALCULATE(SUM ( TableB[Users] ), ALL ( TableB ), DatesSelected ),

    SUM ( TableB[Users] )

)

[jdbuchanan71]

Hello @DataScientist 

I'm not really understanding the problem you are facing.  Can you post your .pbix and highlght the problem and desired outcome?

[DataScientist]

I updated the .pbix file you uploaded with more slicers. There are 4 slicers in total. Still, only table A has the value of "All", table B doesn't. The user can select "All" from any slicers. How to calculate Table B measure in this case?

 

[jdbuchanan71]

Can you share your .pbix file?