cancel
Showing results for
Did you mean:
Regular Visitor

## 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Get data from long fact table using foreign hierarchy

```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] )
)
)
)
)
)```

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.
3 REPLIES 3
Super Contributor

## Re: Get data from long fact table using foreign hierarchy

```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] )
)
)
)
)
)```

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.
Regular Visitor

## Re: Get data from long fact table using foreign hierarchy

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.

Super Contributor

## Re: Get data from long fact table using foreign hierarchy

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.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 256 members 2,540 guests
Recent signins: