cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

Get data from long fact table using foreign hierarchy

I have a long table that contains some dimensions and a value associated to them.

These dimensions have an incomplete hierarchy relation amognst them. In a simple chart I would like to display the value associated with the lowest selected hierarchy level. I include an example with only 1 dimension below (my real cas has 5).

Hierarchy table:

 

LEVEL 1

LEVEL2

LEVEL 3

Food

Fruits

Bananas

Food

Fruits

Apples

Food

Meat

Chicken

Food

Mushrooms

Mushrooms

 

DIMENSION

VALUE

Food

10000

Fruits

2000

Meat

3000

Bananas

500

Apples

500

Chicken

1500

Mushrooms

100

 

My expectation is to have a drilldown that at level 1 shows:

 

X

Y

Food

10000

 

At Level 2

 

X

Y

Fruits

2000

Meat

3000

Mushrooms

100

 

At Level 3 

 

X

Y

Bananas

500

Apples

500

Chicken

1500

Mushrooms

100

 

I believe this logic can be built using RELATED() but I haven't found a way to do so. What is the best way to create such logic in DAX? Is there an approach to embed that in the m power query load side?

Thanks in advance

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft
Microsoft

Hi @Anonymous,

 

Please refer to below measure:

return value =
VAR isLevel1 =
    ISINSCOPE ( 'Hierarchy Table'[LEVEL 1] )
VAR isLevel2 =
    ISINSCOPE ( 'Hierarchy Table'[LEVEL2] )
VAR isLevel3 =
    ISINSCOPE ( 'Hierarchy Table'[LEVEL 3] )
RETURN
    IF (
        isLevel3 = TRUE (),
        CALCULATE (
            SUM ( 'Dimention Table'[VALUE] ),
            FILTER (
                ALLSELECTED ( 'Dimention Table' ),
                'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL 3] )
            )
        ),
        IF (
            isLevel2 = TRUE (),
            CALCULATE (
                SUM ( 'Dimention Table'[VALUE] ),
                FILTER (
                    ALLSELECTED ( 'Dimention Table' ),
                    'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL2] )
                )
            ),
            IF (
                isLevel1 = TRUE (),
                CALCULATE (
                    SUM ( 'Dimention Table'[VALUE] ),
                    FILTER (
                        ALLSELECTED ( 'Dimention Table' ),
                        'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL 1] )
                    )
                )
            )
        )
    )

1.PNG2.PNG3.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yulgu-msft
Microsoft
Microsoft

Hi @Anonymous,

 

Please refer to below measure:

return value =
VAR isLevel1 =
    ISINSCOPE ( 'Hierarchy Table'[LEVEL 1] )
VAR isLevel2 =
    ISINSCOPE ( 'Hierarchy Table'[LEVEL2] )
VAR isLevel3 =
    ISINSCOPE ( 'Hierarchy Table'[LEVEL 3] )
RETURN
    IF (
        isLevel3 = TRUE (),
        CALCULATE (
            SUM ( 'Dimention Table'[VALUE] ),
            FILTER (
                ALLSELECTED ( 'Dimention Table' ),
                'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL 3] )
            )
        ),
        IF (
            isLevel2 = TRUE (),
            CALCULATE (
                SUM ( 'Dimention Table'[VALUE] ),
                FILTER (
                    ALLSELECTED ( 'Dimention Table' ),
                    'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL2] )
                )
            ),
            IF (
                isLevel1 = TRUE (),
                CALCULATE (
                    SUM ( 'Dimention Table'[VALUE] ),
                    FILTER (
                        ALLSELECTED ( 'Dimention Table' ),
                        'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL 1] )
                    )
                )
            )
        )
    )

1.PNG2.PNG3.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hello @v-yulgu-msft,

Thanks a lot, your approach looks close to what I'm looking for.

Is there a more generic way to build this?

I will likely use more than 1 'Hierarchy Table' and perhaps I play around a bit with the number of levels.

Should I make 1 measure per 'Hierarchy Table' and 1 var per level in each hierarchy.

 

Hi @Anonymous,

 


I will likely use more than 1 'Hierarchy Table' and perhaps I play around a bit with the number of levels.

Should I make 1 measure per 'Hierarchy Table' and 1 var per level in each hierarchy.


Yes. You should create one measure per 'Hierarchy Table' and define one variable per level in each hierarchy.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors