Forum Discussion

SteveSteve's avatar
SteveSteve
New Member
3 years ago

Filter 2 columns based on one filter

Hi,

I have a model that contains three tables:

Two of them looks the same and have the same content. "role playing". Only difference is the naming; Product1 and Product2.

Product1(ProductId1, ProductName1)

Product2(ProductId2, ProductName2)

 

Content as below:

Product1(ProductId1, ProductName1)

1, P1

2, P2

3, P3

4, P4

 

Product2(ProductId2, ProductName2)

1, P1

2, P2

3, P3

4, P4

 

The third table called ProductMapping is a fact table ("mapping table") with relationship to the dimensions.


Content of this table as below:

ProductMapping(ProductId1, ProductId2)

1, 2

2, 4

3, 4


I want to filter this table based on the Product1 table. The twist is that I want it to filter both colunns (ProductId1 and ProductId2)

As example if I set a filter on Product1.ProductId1 = 2 then I want the following result:

 

ProductName1, ProductName2, CountBothSide

P1, P2, 1

P2, P4, 1

 

So the measure, CountBothSide, should count rows in the ProductMapping table based on one filter at Product1 table but ”search” for it in both  columns.

I have tested several different approaches but can not get it to work. I'm not sure if this is possible to achieve given the current model? 
Should I use "regular" relationships or some kind of weak relationship?

Major problem that  I'm facing is that when I select/filter on a specifik Product1.Product1Id it will always show only that Product1 in the result.

 

Thanks

Steve

 

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    SteveSteve , Create an independent product table with all products,

     

    ProductName= distinct(union(distinct(Table1[ProductName1]),distinct(Table2[ProductName2])))

     

    Use than in slicer and now join both of table1 and table 2 with table 2 on respective ids

     

    Use this measure with product from both tables in visual

     

    Measure=

    Var _prd1 = Summarize(filter(allselected(Table1), Table1[ProductName1] in values( ProductName[ProductName])),Table1[ProductId1])
    Var _prd2 = Summarize(filter(allselected(Table2), Table2[ProductName1] in values( ProductName[ProductName])),Table2[ProductId2])
    return
    countrows(filter(Table3, Table3[ProductId1] in _prd1 || Table3[ProductId2] in _prd2))