Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tribal
Frequent Visitor

Combine switch functions - hierarchy

I'm currently building a matrix visual where I'm using two tables given below (trending table and waterfall_switch_table) as a basis to switch to required variable using the following measure. 

 

 

 

Trending Measure = 
VAR B1 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B1]
    )
)

VAR B2 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B2]
    )
)

VAR B3 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B3]
    )
)

VAR B4 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B4]
    )
)

VAR B5 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B5]
    )
)

VAR B6 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B6]
    )
)

VAR B7 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B7]
    )
)

VAR B8 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B8]
    )
)

VAR B9 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B9]
    )
)

VAR B10 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B10]
    )
)

VAR B11 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B11]
    )
)

VAR B12 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B12]
    )
)

VAR B13 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B13]
    )
)

VAR A1 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[A1]
    )
)

VAR A5 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[A2]
    )
)

VAR A2 =
B1 + B2 + B3 + B4 + B5 + B6 + B7

VAR A3 = 
B8 + B9 + B10

VAR A4 =
B11 + B12 + B13

RETURN
IF(
    ISINSCOPE(Trending[Sub var]),
    SWITCH (
        SELECTEDVALUE( Trending[ID] ),
        1, B1,
        2, B2,
        3, B3,
        4, B4,
        5, B5,
        6, B6,
        7, B7,
        8, B8,
        9, B9,
        10, B10,
        11, B11,
        12, B12,
        13, B13
    ),
    SWITCH (
        SELECTEDVALUE( Waterfall_SwitchTable[ID] ),
        1, A1,
        2, A2,
        3, A3,
        4, A4,
        5, A5
    )
)

 

 

 

This produces the following matrix, where the values are shown when expanded to level B. But consequently all level A values are hidden and will only be shown when the table is fully collapsed. I've been trying to combine these two switch functions, but haven't been able to unfortunately. Could someone steer me into the right direction to make this work?

Expanded:

Tribal_0-1699351414371.png


Collapsed:

Tribal_1-1699349568932.png

 

Trending table:

Tribal_2-1699349647418.png

 

Waterfall_Switch_table:

Tribal_3-1699349683612.png

 

Sample data - Budgetvolume table:

A1A5B1B2B3B4B5B6B7B8B9B10B11B12B13
20303456789101112131415
20303456789101112131415
20303456789101112131415
20303456789101112131415
20303456789101112131415
20303456789101112131415

 

1 ACCEPTED SOLUTION

Maybe you just need to re-enable row totals?

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

IF(
    ISINSCOPE(Trending[Sub measure]),

 

Measures are never in scope.

 

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...


https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

 

Thank you for the feedback, in hindsight I see I didn't provide enough information. I changed the naming of all variables, tables, measures and values. So nothing is blacked out anymore and the entire measure is now visible.

 

Related to your reply regarding measures not being in scope. That would be correct, but I named the column a bit vaguely as it is not referring to a measure but the names of the variables. The provided value within 

 

 

 

IF(
    ISINSCOPE(Trending[Sub var]),

 

 

refers to a table[column] reference, so that shouldn't be the issue here

Maybe you just need to re-enable row totals?

O I feel kind of stupid now, I was trying to make the solution way too complicated. Thank you, that was it

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors