Filter by values in different rows

[Kohrinn]

Hi,

I have a problem with filtering in such a scenario:

I have materials to be produced. Each material has its own MaterialID. Production of a material requires at least one recipe. Each recipe uses certain number of resources.

Each row in my table Production stores information about produced material, recipe and used resources. If there's 3 recipes to produce one material, there will be 3 rows in my table. Table Resources stores all available resources.

By clicking on first visual, I select produced material by its ID. The visual below shows recipes that belong to this MaterialID.

I've made a measure Filter Only Resources From Recipe that filters out resources for selected recipe in the third visual. What I want to achieve now is to show all the resources that are used to produce selected MaterialID, not only for one recipe. How to do it?
BTW: Is that any better way then what I've made in my measure to filter resources?

Here's my pbix : Link 

Thanks,

Joanna

[gmsamborn]

Hi @Kohrinn 

By unpivoting the Production table and creating a couple of dimension tables and indexes, I did it with no measures.

Resources - test.pbix

[smpa01]

@Kohrinn  you can do something like this

Measure = CONCATENATEX(Production,Production[Recipes]&"-"&Production[UsedResource01]&","&Production[UsedResource02]&","&Production[UsedResource03]," || ",Production[Recipes],ASC)

[Kohrinn]

Thank you, but it's not what I meant.

This is what's in my table:

So I know that in order to produce 111 I need A,J,K,C,D,G,H,I. So when I click on my visual with MaterialID and Iwant to have resources filtered like that:

[smpa01]

@Kohrinn  you can use this a filter

Measure = 
MAXX (
    NATURALINNERJOIN (
        SUMMARIZE (
            ADDCOLUMNS (
                FILTER (
                    UNION (
                        SELECTCOLUMNS ( 'Production', 'Production'[UsedResource01] ),
                        SELECTCOLUMNS ( 'Production', 'Production'[UsedResource02] ),
                        SELECTCOLUMNS ( 'Production', 'Production'[UsedResource03] )
                    ),
                    'Production'[UsedResource01] <> BLANK ()
                ),
                "col", [UsedResource01]
            ),
            [col]
        ),
        SUMMARIZE (
            SELECTCOLUMNS ( { MAX ( Resources[ResourceID] ) }, "col", [Value] ),
            [col]
        )
    ),
    [col]
)