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

Need Help

4 Replies

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