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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
markblom
Frequent Visitor

Combine data from hierarchy

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

 

markblom_0-1672054212034.png

 

 

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?

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @markblom 

please try

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

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

Hi @markblom 

please try

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

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?

 

markblom_0-1672136967955.png

 

@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
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.