Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
Let's say I have these 3 tables:
Article
ArticleSecondary
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
I have defined the following relationships:
Relationships:
My goal is to create a table visual with 3 columns:
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?
Solved! Go to 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 )
)
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
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.
@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
Thanks for the reply! I will have a look at the youtube movies in your links.
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
28 | |
28 | |
13 | |
12 | |
12 |