SWITCH Function in Matrix not working

[ChrisFromOhio]

Hello all - I am attempting to build out a matrix visualization with a tooltip chart that will change based on the metric the user is hovering over. In my dataset (called Sales), I have a variety of measures (Sales $, Sales U, Cost $). I want to add a fourth measure, which is Margin % (Cost $/Sales $). I am able to do this if I create the measures in the Sales table using the calculate function. However, I cannot get a tooltip chart to change the visualization based on the value selected.

Therefore, my workaround was to create a separate table called Metrics, with the four metrics listed above. I then created a SWITCH measure, depending on which metric was selected in the table, would present that calculation. I had to create a relationship between the metric names in the Metric table and the measure names in the Sales table. 

This works great, except for the calculated measure of Margin %. I'm assuming this is because there is no Margin % measure name in the Sales table. Are there any solutions out there that would solve this problem?

Sample PBI file here.

[Anonymous · Accepted solution]

Hi  @ChrisFromOhio ,

Not quite sure what you mean, you might consider displaying the corresponding MEASURES based on the slicer selection

Here are the steps you can follow：

1. Use Enter data to create a table with no join relationships that contains the names of the four measure：

2. Create measure.

Measure =
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
RETURN
SWITCH(
    TRUE(),
_select = "Sales $",[Sales $],
_select = "Sales U",[Sales U],
_select = "Cost $",[Cost $],
_select = "Margin %",[Margin %])

3. Result:

If the results above don't match your expected results, you can express your expected results in the form of a picture and we can help you better!

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ChrisFromOhio]

Hi @Anonymous

Thank you for your reply. Solution was to break the relationship. Thanks!