Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi !
I have 2 tables, Table A shows part number applicability , Table B shows stock levels.
I want to create a report that shows P/N and q-ty per selected AC type. I tried (snip below) but i get P/N A listed twice (and double q-ty) because it is linked to 2 AC types.
Any help appreciated! 🙂
P/N | AC type |
A | 737 |
A | 787 |
B | 737 |
C | |
D | 787 |
P/N | Qty |
A | 10 |
B | 1 |
C | 2 |
D | 54 |
Solved! Go to Solution.
Have updated the demo file. The quanity measure needed to read the p/n currently needed...
Quantity =
CALCULATE(
SUM ( Stock[Qty] ),
TREATAS( VALUES(Applicability[P/N]), Stock[P/N] )
)
Could have made relationship bidirectional but this is safer.
hi bcdobbs
i will create button shape filters for each AC type:
Depending on selected button(s), here is what i expect to see:
for single selections its not a problem, its multiple AC types selected that give wrong total q-ties.
thanks 🙂
Have a go with this demo: Applicability Demo
Rather than dropping the A/C column into the visual use this measure instead (and use P/N from your stock table):
Applicable Types =
VAR Result =
CONCATENATEX(
VALUES(Applicability[AC type]),
Applicability[AC type],
", ",
Applicability[AC type], ASC
)
RETURN IF ( ISINSCOPE(Stock[P/N]), Result, BLANK() )
that looks very promising!! not sure if it would be possible for ex. that only selected AC types are shown in the table.. i selected 787 and total q-ty is 67. if non relevant where hidden then it would be perfect
I think my change to the quanity measure mentioned below does what you need.
Hoping latest version has now synced up to cloud! Give it a minute and download a fresh copy from Applicability Demo
Have updated the demo file. The quanity measure needed to read the p/n currently needed...
Quantity =
CALCULATE(
SUM ( Stock[Qty] ),
TREATAS( VALUES(Applicability[P/N]), Stock[P/N] )
)
Could have made relationship bidirectional but this is safer.
PERFECT!!! thank you so much bcdobbs! you are genious 🙂
Pondering on your data model.
I think it's a legitimate use case of a bidirectional relationship. Especially if you only have those two tables. Would remove the need for the TREATAS.
hi bcdobbs
yes, birectional is the only available i can chose..
i havent used the Quantity measure and it adds up correctly.
The next challenge, how could i filter if i want by A/C type that is unique : ex. 787 only.
so in my initial example, by chosing 787 it would normally show only D; A would be excluded as its both 787 and 737.
I think the easiest to solve this is by using filter "does not include" in applicable types ? 😊
Rgds
Aris
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
124 | |
79 | |
49 | |
38 | |
37 |
User | Count |
---|---|
196 | |
80 | |
70 | |
51 | |
42 |