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.
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
Solved! Go to 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
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
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |