Stuck Creating a SWITCH measure

[CMoppet]

Hello 😊

Please can I lean on this great community again to help me with a project I am working on?  I have attached sample data to demonstrate the issue I am having:  https://drive.google.com/file/d/1BBGdvVPaE0-z0fUCAlUbuHxn3wxQnRLw/view?usp=drive_link

The table and chart in the document work exactly as required in most respects, where a user can slice the bar chart according to a number of different variables.  However, I am unable to get the final ‘Hard or Soft?’ slicer to work, highlighted yellow in the document.

In a previous post, the very helpful @amitchandak advised that I may need to use the ‘SWITCH’ function to create a measure for this slicer, but I’ve watched several videos and read posts and am unsure how to apply the logic to my scenario.

Please can someone help me create a measure so that the ‘Hard or Soft’ slicer is functional too?  To clarify, if someone selects ‘Hard’ in the slicer, the denominator in the MTBF measure (a DIVIDE measure) should change to only count repairs where the ‘Hard or Soft’ row value is ‘Hard’.  The numerator should remain the same.

Many Thanks

[lbendlin]

Please explain your data model.

Please read about normalization and star schemas.

[CMoppet]

@lbendlin  Hello.  Thanks for taking the time to have a lookat this 🙂

I have connected the columns that are common to both tables; Machine Brand, Type, Age Bracket, Market, Supplier.

[Anonymous]

Hi, @CMoppet 

Excuse me, did lbendlin 's answer solve your problem? If it has already been resolved, consider accepting him as a solution. Of course, if you have any solutions, you are also welcome to share.

Based on my analysis of your data, it is also recommended that you rearrange the relationships between the data tables so that when you use the Hard or Soft column of the Repairs table as a slicer, you can also filter to the metrics related to the BaseSummary table.

Here are the links to the mentioned stellate structures that I hope will be helpful to you:

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[lbendlin]

Those common columns must come from their own dimension tables.  Do not link fact tables, ever.