How to create Power BI tabbed visuals with or without common slicers

   Check list of all posts

Requirement

It is good to organize visuals in tabs, where each tab represents a major business case or KPI. But, behind the scene, it is a different fact. The screenshot below is a sample layout.

This report has three major requirements:

1. Users can click different tabs to go to different visuals.

2. all Slicers are cascadable with correspondent facts

3. The measure calculation can be very complicated other than simple aggregation.

There are two major cases: 1) All slicers ( dimensions) are shared with all fact tables; 2. Slicers are different based on individual facts.

Option 1: With Common Filters - Bookmark approach

Bookmarks

As we want setup bookmarks with shared dimensions, we should disable data and all visuals as below.

Button Action to Bookmark

Each button will assign to action with Bookmark.

Filter slicers without using bidirectional filters

The best way to make the slicer cascadable is to define a measure below and assign it to each slicer. Then, if these slicers have the same combinations, we can use Fact 1 to represent all other 3 facts, or fact 2,3, and 4.

FilterSlicersFact1 = 
INT ( NOT ISEMPTY ( Fact1 ) )

Option 2: Without Common Filters - Page approach

Pages

As dimensions don't share with different facts, or they have different combinations. In this case, we can use the shared dimensions ( slicers) but with different filters. They will have different pages. 

Each button will assign to action with Page navigation.

Filter slicers without using bidirectional filters

These measures will be used for different pages, respectively.

FilterSlicersFact1 = 
INT ( NOT ISEMPTY ( Fact1 ) )

FilterSlicersFact2 = 
INT ( NOT ISEMPTY ( Fact2 ) )

FilterSlicersFact3 = 
INT ( NOT ISEMPTY ( Fact3 ) )

FilterSlicersFact4 = 
INT ( NOT ISEMPTY ( Fact4 ) )

Synchronize Slicers

Make sure that all slicers are NOT in sync with all pages, as they are different, page by page

Advanced Dax script

1. Complicated measure 1 (ASF)

DEFINE 

---- MODEL MEASURES BEGIN ----
MEASURE _Measures[Day0ASF] = 
VAR CurrencyCode =
    SELECTEDVALUE ( Dim1[Currency] )
VAR result =
    CALCULATE (
        MAX ( Day0BaseValue[Day0_ASF] ),
        Day0BaseValue[Currency] = CurrencyCode
    )
RETURN
    COALESCE(result,0.0)
MEASURE _Measures[NumberOfRowsInFact4] = 
COUNTROWS('Fact4')
MEASURE Fact4[ASF File] = 
SUM ( 'Fact4'[ASF1] )
---- MODEL MEASURES END ----

MEASURE Fact4[ASF] = 
VAR bAverageOrSpot =
    SELECTEDVALUE ( View[View] )
VAR bShow =
    CONTAINS ( 'Fact4Measures', Fact4Measures[Measure], "ASF" )
VAR ValidCombinations =
    FILTER (
        SUMMARIZE (
            Fact4,
            'Dim3'[Product],
            'Dim4'[LCR Level],
            'Dim5'[Counterparty Type],
            'Dim6'[Encumbrance Status]
        ),
        [NumberOfRowsInFact4] <> 0
    )
VAR NumberOfValidCombinations =
    COUNTROWS ( ValidCombinations )
VAR BaseAmt = [Day0ASF]
VAR MinusAmt = BaseAmt * ( NumberOfValidCombinations - 1 )
RETURN
    IF (
        bShow,
        IF (
            bAverageOrSpot = "Average",
            AVERAGEX (
                VALUES ( 'Date'[Date] ),
                IF ( ISBLANK ( [ASF File] ), BLANK (), [ASF File] - MinusAmt )
            ),
            CALCULATE (
                IF ( ISBLANK ( [ASF File] ), BLANK (), [ASF File] - MinusAmt ),
                LASTDATE ( 'Date'[Date] )
            )
        ),
        BLANK ()
    )

2. Complicated measure 1 (Balance)

DEFINE 

---- MODEL MEASURES BEGIN ----
MEASURE _Measures[NumberOfRowsInCashBalance] = 
COUNTROWS ( Fact1 )
MEASURE Fact1[Cash Balance File] = 
AVERAGE ( 'Fact1'[Balance1])
---- MODEL MEASURES END ----

MEASURE Fact1[Cash Balance] = 
VAR bAverageOrSpot =
    SELECTEDVALUE ( View[View] )
VAR bShow =
    CONTAINS ( 'CashFlowMeasure', CashFlowMeasure[Measure], "Balance" )
VAR ValidCombinations =
    FILTER (
        SUMMARIZE (
            Fact1,
            'Product'[Product],
            'Term Bucket'[Term Bucket]
        ),
        [NumberOfRowsInCashBalance] <> 0
    )
RETURN
    IF (
        bShow,
        IF (
            bAverageOrSpot = "Average",
            SUMX ( ValidCombinations, [Cash Balance File] ),
            SUMX (
                ValidCombinations,
                CALCULATE ( [Cash Balance File], LASTDATE ( 'Date'[Date] ) )
            )
        ),
        BLANK ()
    )

3. Filter description

DEFINE 

MEASURE _Measures[FiltersFact4] = 
VAR Dim3Selected =
    COUNTROWS ( DISTINCT ( ALLSELECTED ( 'Dim3'[Product] ) ) )
VAR Dim3Total =
    COUNTROWS ( DISTINCT ( ALL ( 'Dim3'[Product] ) ) )
VAR Dim4Selected =
    COUNTROWS ( DISTINCT ( ALLSELECTED ( 'Dim4'[LCR Level] ) ) )
VAR Dim4Total =
    COUNTROWS ( DISTINCT ( ALL ( 'Dim4'[LCR Level] ) ) )
VAR Dim5Total =
    COUNTROWS ( DISTINCT ( ALL ( 'Dim5'[Counterparty Type] ) ) )
VAR Dim5Selected =
    COUNTROWS (
        DISTINCT ( ALLSELECTED ( 'Dim5'[Counterparty Type] ) )
    )
VAR Dim6Total =
    COUNTROWS ( DISTINCT ( ALL ( 'Dim6'[Encumbrance Status] ) ) )
VAR Dim6Selected =
    COUNTROWS (
        DISTINCT ( ALLSELECTED ( 'Dim6'[Encumbrance Status] ) )
    )
RETURN
    "Data Filters : " & SELECTEDVALUE ( 'Dim1'[Currency] ) & " > "
        & SELECTEDVALUE ( 'Dim2'[Scenario] ) & " > "
        & IF (
            Dim3Selected = Dim3Total,
            "All Dim3s",
            CONCATENATEX ( 'Dim3', 'Dim3'[Product], " & " )
        ) & " > "
        & IF (
            Dim4Selected = Dim4Total,
            "All Dim4s",
            CONCATENATEX ( 'Dim4', 'Dim4'[LCR Level], " & " )
        ) & " > "
        & IF (
            Dim5Selected = Dim5Total,
            "All Dim5s",
            CONCATENATEX (
                'Dim5',
                'Dim5'[Counterparty Type],
                " & "
            )
        ) & " > "
        & IF (
            Dim6Selected = Dim6Total,
            "All Dim6s",
            CONCATENATEX (
                'Dim6',
                'Dim6'[Encumbrance Status],
                " & "
            )
        ) & " > " & "from "
        & CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) ) & " to "
        & CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )