Forum Discussion

Kohrinn's avatar
Kohrinn
Helper I
2 years ago

Filter by values in different rows

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 ...
Need Help
Kohrinn's avatar
Kohrinn
Helper I
2 years ago

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's avatar
smpa01
Community Champion
2 years ago

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]
)