Forum Discussion
Join two slicer like OR gate
Hi Tnt,
You're welcome 🙂
The reason for manipulating the data model this way is that using physical relationships for filtering generally gives better performance, and reduces the complexity of any measures that need to be written, compared with other approaches where relationships are simulated using DAX.
I have actually attached an updated PBIX, just with the query steps changed for the 'Benchmark Product' table. The final data model is unchanged, however.
For the 'Benchmark Product' table, if you take a look in the Power Query editor (Transform Data), the query begins with a Source step that is your original table. The subsequent steps duplicate the columns then perform an unpivot operation (and remove one unwanted column) to produce the final table.
As long as you point that Source step to your original data source for that table, the final 'Benchmark Product' table will update correctly on refresh. However, it would be worth testing performance with your actual data to confirm it refreshes in an acceptable time.
I tested this query structure with a SQL Server source at my end, and the steps (including Unpivot) were able to fold, meaning Power Query translated the query into a SQL statement that can be run on the server.
It is possible to what you wanted to do without manipulating the data model, and instead writing appropriate measures in DAX. Would you like an example of that?
Regards,
Owen
Hi Tnt
I would suggest tweaking your data model as follows:
1. Expand your first table (which I will call Benchmark Product) to include an additional Product column, which contains the values of My Product and Benchmark Product from the original row. This means that each original row expands to two rows, as shown below.
| My Product | Benchmark Product | Product |
| AA | XX | AA |
| AA | XX | XX |
| AA | YY | AA |
| AA | YY | YY |
| BB | YY | BB |
| BB | YY | YY |
| BB | ZZ | BB |
| BB | ZZ | ZZ |
2. Create a 'Product' table with the distinct values from the Product column.
| Product |
| AA |
| XX |
| YY |
| BB |
| ZZ |
3. Create relationships like this, with a bidirectional relationship between Benchmark Product and Product
4. Then in the report, you can create separate slicers for My Product and Benchmark Product, which will naturally filter each other. Then the filtered values of Product will filter the fact table (Test results above).
5. You may also want to Edit Interactions on the slicers so My Product filters Benchmark Product, but not vice versa. This would mean that selecting My Product = AA reduces the Benchmark Product slicer to XX & YY, but selecting XX doesn't reduce the My Product slicer to just AA.
6. You could also consider a combined slicer where My Product & Benchmark Product are displayed in a hierarchy.
Hope that helps!
Regards,
Owen
Hi Owen,
Thank you for your suggestions. I appreciate your solution of manipulating data model.
However, i would like to know how feasible is it for a data set with many hundreds of data. Could you suggest any scalable way by which I could create the first table you suggested.
Thanks
Tnt
