Unsure how to add final Variable to Custom Column

[CMoppet]

Hello 🙂

I have the following 3 measures in a table called 'WO Raw Data':

Hard Repairs = CALCULATE(COUNTROWS('WO Raw Data'),FILTER('WO Raw Data',[MTBF Repair?]="Repair" && [HARDSOFT]="Hard"))

Soft Repairs = CALCULATE(COUNTROWS('WO Raw Data'),FILTER('WO Raw Data',[MTBF Repair?]="Repair" && [HARDSOFT]="Soft"))

RepairsX = [Hard Repairs] + [Soft Repairs]

I then use the final measure above (RepairsX) in a custom column in a second table called 'BaseSummary':

Repairs2 = 
VAR MonthYear = [Month/Year]
VAR MachineBrand = [Machine Brand]
VAR MachineType = [Machine Type]
VAR Age = [Age Bracket]
VAR Market = [Market]
VAR Supplier = [Supplier]
VAR _Results = SUMX(FILTER(ALL('WO Raw Data'),'WO Raw Data'[Age Bracket of Mc at Failure]=Age && [Supplier]=Supplier && [Machine Brand]=MachineBrand && [Machine Type]=MachineType && [Country]= Market && [Month]=MonthYear),'WO Raw Data'[RepairsX])
RETURN
_Results

In a bar chart, I can slice by all of the VARiables in the Repairs2 custom column, but I also need to be able to slice by the HARDSOFT variable from the 'WO Raw Data' table too.  

Please can someone help me add this HARDSOFT variable to the Repairs2 custom column above so it can be sliced?  There isn't a HARDSOFT column in the BaseSummary table, so I don't know how to model it so they connect.

Many Thanks

[amitchandak]

@CMoppet , You can not use slicer values in the calculated column. Which means measures can not used with dynamic values.

If you want switch measures, you can use field parameters or calculation groups 

or you have to create Repair 2 as measure along with table with measure names that allow you select a measure in case you want to switch

Dynamic Segment can help

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4

Or Switch measures when using field parameters can help the code below, which can use in repairs 2

Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f

[CMoppet]

@amitchandak  Thank you for taking the time to reply.  I've looked at two of the videos but I don't know how to apply it to this scenario.  I'll find another way around it, maybe create a new table that summarises everything I need and work from there.  Thank you again for your time 🙂

[Anonymous]

Hi,amitchandak ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@CMoppet .

Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions.
Thank you very much for your understanding and support of Power BI.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.