The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
78 | |
66 | |
52 | |
51 |
User | Count |
---|---|
127 | |
116 | |
78 | |
64 | |
63 |