Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Following scenario:
I have spare part items in one table. They are of some type like "engine", "mirror" etc. There are another table "Items_Cars" where those spare parts are refered to cars, in which they can be used.
What I want to calculate for the "Items" table now is, how many alternative parts exist for each item in the table? An item is alternative if it's of the same type and can be used in the same car.
For item "4711" I have to do following:
- 4711 --> cars 1,2,3
- car 1 --> 4711 (same item), 4712 (alternate item!), 4713 (alternate item!), 4714 (wront type)
- car 2 --> 4711 (same item), 4712 (alternate item!), 4715 (wrong type)
- car 3 --> 4711 (same item), 4715 (wrong type)
- Count alternate parts: 2 (do not count 4712 twice!)
How can I do this with DAX?
And one general question: Do I have variables, which I can use in the current row context? For example "filter table Items_Cars where ItemNr = [ItemNr value of the current row in the iteration]" ?
Solved! Go to Solution.
Hi @eniX
Try this Calculated Column in ITEMS TABLE
What are you expected RESULTS?
Column = VAR myitemType = Items[Type] VAR myTable = CALCULATETABLE ( VALUES ( Items_Cars[CarNr] ) ) RETURN COUNTROWS ( CALCULATETABLE ( DISTINCT ( Items_Cars[ItemNr] ), FILTER ( Items, Items[Type] = myitemType ), FILTER ( VALUES ( Items_Cars ), CONTAINS ( myTable, Items_Cars[CarNr], Items_Cars[CarNr] ) ) ) ) - 1
Hi @eniX
Try this Calculated Column in ITEMS TABLE
What are you expected RESULTS?
Column = VAR myitemType = Items[Type] VAR myTable = CALCULATETABLE ( VALUES ( Items_Cars[CarNr] ) ) RETURN COUNTROWS ( CALCULATETABLE ( DISTINCT ( Items_Cars[ItemNr] ), FILTER ( Items, Items[Type] = myitemType ), FILTER ( VALUES ( Items_Cars ), CONTAINS ( myTable, Items_Cars[CarNr], Items_Cars[CarNr] ) ) ) ) - 1
Works like a charm! Thx