Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
eniX
Helper III
Helper III

DAX formula problem and one general question

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. 

 

1.png2.png 

 

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]" ?

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

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

1046.png


Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

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

1046.png


Regards
Zubair

Please try my custom visuals

Works like a charm! Thx Smiley Very Happy

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors