Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Filtering one table with 3 different columns from other

Hello, 

I'm a beginner in Power BI (low knowledge in DAX functions yet), and I would like to ask for some help in the challenge I'm facing:

 

I have two different tables: "Recommendations" and "Knowledge Database".

In the table "Recommendations", I have the 4 columns that I'm using as the slicers to filter my report accordingly to the image below (columns System, Size, Format, Opening). The first matrix visual (in red) belongs to the same table than the slicers, so until here I haven't had any problem. 

In the second table (Knowledge Database), I have the same 4 columns with one additional column called "Information".

My challenge here is that I want to show in the table in green the results of this "Information" column filtered by the slicers which belong to the "Recommendations" table. The problem is that the Power BI allows me only to create a relationship between one column of each table, so I have to choose only one slicer to use as filter, but I need to be able to filter the results using a mix of all the 4 slicers. (E.g. Filtering the System "TBA", Size "200", Format "All", Opening "All"; or filtering System "All", Size "400", Format "Base", Opening "All")

Below you can see the images of both tables (First Image: Recommendations, Second Image: Knowledge Database)

           

 

Is there any way to perform such filter?

 

Thanks in advance!!

DAX
Filter
Multiple columns

4 Replies

  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    Icon for Solution Specialist rankSolution Specialist
    5 years ago

    You can create a measure to evaluate the filters

     

    Filter =
IF ( SELECTEDVALUE ( 'Knowledge Database'[SYSTEM] ) in VALUES ( Recommendations[SYSTEM] ) &&
SELECTEDVALUE ( 'Knowledge Database'[SIZE] ) in VALUES ( Recommendations[SIZE] ) &&
SELECTEDVALUE ( 'Knowledge Database'[FORMAT] ) in VALUES ( Recommendations[FORMAT] ) &&
SELECTEDVALUE ( 'Knowledge Database'[OPENING] ) in VALUES ( Recommendations[OPENING] ),
"Keep", "Remove")

     

    Then apply this measure to the visual filter and set it to Keep

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks a lot for your support!

      However what if I need to select multiple values in my slicer? I mean, if in the "System" slicer I need to select "TBA" and "TCA". The SelectedValue expression does not allow me to get more than 1 value, and the "in" expression (I'm not sure) also only check for one argument, right?

      • Mohammad_Refaei's avatar
        Mohammad_Refaei
        Icon for Solution Specialist rankSolution Specialist
        5 years ago

        The syntax should allow you to select multimple values since it is evaluating against all the available values in your target table (in VALUES ( Recommendations[SYSTEM] )

         

        The selectedvalue is used at the row level in your 'Knowledge Database' table to filter each row against your selection.

         

        Give it a try, it should work.