Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
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
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?
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.
Hello @Mohammad_Refaei ,
I tried to apply the filter but, unfortunately, it is not working. I did exactly as you suggested (only changed the "," to ";" due to Power BI local configurations):
When I have all options selected on the slicers or only one option like "TBA" in System, the measure Mod Filter always returns "Remove". I've included a table to check the Mod Filter return (image below)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |