Multiple level filter

[TheoM]

Hello all,

I have 2 tables in my data model:

Items and Bill of materials

I have an active relation between Items and bill of materials, in bill of material table, this relation is on items used. So when I select an item from my items table, I get a "where used" list, which is a list of all items that have this particular item in the bill of materials.

So far so good. However, there are several levels in the bill of material table. For example: I have items A, B, C and D. I need A to make B, so A is on the bill of materials of item B. When I apply the filter on item A, it shows B on my where-used list. However, I need B to make C, B is on the bill of materials of item C, but I want C to also show up on my where-used list, because in the end A is needed to make item C. The number of levels can vary from 1 to 6. Does anyone have an idea how to create a complete "where used list"?, so when I select item A, my where used list contains item B and C?

Best regards,

Theo

[GeorgeBuster]

Hi TheoM,

It sounds like the common hierarchy issue. I going to try explaining one solution. You can achieve the table that I going to explain doing the necessary transformations in the Power BI query editor.

I think about a table with 2 columns, one with the attribute that you will use in the filter visualization and other with the items who filter your item table (this last column will have the relationship with the item table).

Let me explain better with an example, this would be the table:

FILTER ITEM                 ITEM

A                                  A

A                                  B

A                                  C

B                                  B

B                                  C

....                                 ....

With this table, you could put in the PBI filter visualization field the FILTER ITEM, but with your measure depending on the ITEM column. In this way when you select A, the table gives A, B and C items.

Hope you can achieve your goal.
Jorge.

[TheoM]

Hello Jorge,

Thanks for your reply. However, reality is a bit more complicated. I am afraid my example was a bit too simple. In fact I am dealing with a "loop" problem. 

See part of my BOM table below. When I select Item STR150X5/6100, I need to filter my BOM table on STR150X5/6100, but also on the items that have STR150X5/6100 on the bill of materials. That means I also want to filter on RWS0025, RWS0030, RWS0120, RWS6040, and so on.

Is it possible to filter the table on the Items needed column from my items table (filter 1), take the result and filter the table again with the result of filter 1 (= filter 2) and so on until finally the result is empty. Finally I want to combine the results of filters applied.

Any ideas how to solve this?

Theo

[GeorgeBuster]

Hi TheoM,

As far as I know, DAX language doesn't have loop functions as other languages have (functions like for, while, break...). In DAX you can do iterations, but they need to have a defined number of iterations.

[TheoM · Accepted solution]

Hi,

I think I found a workaround myself to fix this. I copied the bill of materials table and then I join the two tables to get the bill of materials of the items that are on my first bill of materials list. When I repeat this 5 or 6 times, I reached the most detailed breakdown. I need to adjust my query, so it returns the original item when this item does not have a bill of materials itself, the below table is the quick and dirty result.