topic Re: Combine data from hierarchy in DAX Commands and Tips

Combine data from hierarchy

markblom — Mon, 26 Dec 2022 14:10:21 GMT

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?

Re: Combine data from hierarchy

amitchandak — Mon, 26 Dec 2022 12:33:27 GMT

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

Re: Combine data from hierarchy

markblom — Mon, 26 Dec 2022 14:56:34 GMT

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

Re: Combine data from hierarchy

tamerj1 — Mon, 26 Dec 2022 18:07:18 GMT

Hi markblom

please try

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

Re: Combine data from hierarchy

markblom — Tue, 27 Dec 2022 10:30:13 GMT

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?

Re: Combine data from hierarchy

tamerj1 — Tue, 27 Dec 2022 10:54:08 GMT

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.