Combine data from hierarchy

[markblom]

Hi,

Let's say I have these 3 tables:

Article

• ArticleId
• ArticleCode

ArticleSecondary

• PrimaryArticleId
• SecondaryArticleId

This table is the link between a Primary Article with zero, one or more Secondary Articles. We use this table to define alternatives for our main choice of articles.

Orders

• Id
• ArticleId
• Quantity

I have defined the following relationships:

Relationships:

• Article.ArticleId -> Orders.ArticleId
• Article.ArticleId -> ArticleSecondary.PrimaryArticleId
• ArticleSecundary.SecondaryArticleId -> Orders.ArticleId

My goal is to create a table visual with 3 columns:

• ArticleCode
• Quantity Ordered For Primary Article
• Quantity Ordered For Secondary Article

I get stuck with the quantity for the secondary articles. This column is empty and I suspect that it has to do with the fact that Orders is already filtered by ArticleId from the Articles table.

Any tips on how I can get the correct order quantities for the secondary articles?

[tamerj1 · Accepted solution]

Hi @markblom 

please try

Quantity Ordered For Secondary Article =
CALCULATE (
SUM ( Orders[Quantity] ),
USERELATIONSHIP ( ArticleSecundary[SecondaryArticleId], Orders[ArticleId] ),
CROSSFILTER ( Article[ArticleId], Orders[ArticleId], NONE )
)

[markblom]

This is working for me, thanks!!!

One minor issue though:

First row contains a value for Secondary orders quantity but no ArticleCode. Any idea what is causing this?

[tamerj1]

@markblom 
Power Bi automatically creates a blank row to group the quanities that do not belong to any secondary id. Just filter out the blank row using the filter pane.

[amitchandak]

@markblom , this is creating loop. You should not join

Article.ArticleId -> ArticleSecunday.PrimaryArticleId

In case you want to filter the slicer of ArticleSecunday , you can try

How to filter the slicer of a disconnected table: https://youtu.be/cV5WfaQt6C8

https://www.youtube.com/watch?v=cyOquvfhzNM

[markblom]

Thanks for the reply! I will have a look at the youtube movies in your links.