Join two slicer like OR gate

[Tnt]

Hi Experts,

I am a novice in powerbi and facing a problem. Plese find the details of the problem for which I am unable to realize the solution.

I have two or our product - AA and BB, which need to be compared individually with Competitor products XX, YY, and ZZ (see table below of possible comparisons)

My Product	Benchmark Product
AA	XX
AA	YY
BB	YY
BB	ZZ

I created two Dimension table, one for my product and one for competitor products. The selection on our product (slicer) will refine the options in  competitor products (second slicer).

For eg: if i select AA in prodcut the options in second slicer will be XX and YY but not ZZ (as its not benchmarked against).

Now I need to select either XX or YY of our competitor product.

The selection then needs to identify related data from a fact table as shown below.

Product	Test result
XX	8
YY	9
ZZ	7
AA	7
BB	8

For selection AA in first slicer and XX in second slicer, visualizer (Table/column) should give values for the selection.  (like the table below)

Product	Test result
AA	7
XX	8

Could you help me with this.

Thanks for your support.

Regards

Tnt

[OwenAuger · Accepted solution]

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

[OwenAuger]

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

[Tnt]

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