Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |