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

Kohrinn  you can do something like this

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

 

 

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