Switch True() - Deciding between 2 calculations

[stiefel.ed]

Trying to have a measure calculate a new column. I am using switch true to have it determine between two equations.

Colum A for this example is Cycle Time
Column B is mold cavitation

I am trying to calculate throughput. If Mold Cavitation is blank or 0, I want this equation:
(60/Cycle Time)*60.

If Mold Cavitation is NOT Blank and is above 0, I want it to multiply the original equation by the number of cavities:
(60/Cycle Time)60Mold Cavitation.

I have tried so many variations but it always comes back with “too many columns, cannot return a scalar value”.

I have attached an example where I directly used filters and equations in the Switch True formula, and an example where I used other measures, with those measures attached. Please help!

[Greg]

Hi @stiefel.ed.

To help us further analyze your current state and visualize your issue, could you please provide as many as you can of:

• Your work-in-progress PBIX file, using sanitized data if necessary
  • here’s @BrianJ’s YouTube video on how to anonymizing data in a Power BI file: https://www.youtube.com/watch?v=VmWD7Ayw_NI
• Your dataset as an Excel file (again, sanitized if necessary)
• A detailed mock-up (marked-up screenshot or Excel file) of your desired outcome.

Also, if you provide DAX in your post, please format it using the built-in formatter.

Greg

[lsquintana17]

This error is happening because the DAX expression is expecting a single value but gets a table of values instead.
I’m sure you have done your research, but this article seems like it may help you: (https://www.sqlgene.com/2019/04/30/dax-error-the-expression-refers-to-multiple-columns-multiple-columns-cannot-be-converted-to-a-scalar-value/)

[stiefel.ed]

Calculate- Averagex- Then the Switch True () worked! I figured it out. Thank you!

[lsquintana17]

If you wouldn’t mind, please post the calculation that worked for you. It appears that you just changed the order of the calculation and it worked, however, I’m sure I will encounter this error sometime in the future and would love some insight into how you were able to resolve the error.
Thank you.

[stiefel.ed]

Here is what worked for me:
AVG Conventional Throughput =
CALCULATE(
AVERAGEX(‘Case Study Database’,
SWITCH(
TRUE(),
NOT(ISBLANK(‘Case Study Database’[MoldCavitation])) && ‘Case Study Database’[MoldCavitation]>0, DIVIDE(60,‘Case Study Database’[ConventionalCycleTime])60’Case Study Database’[MoldCavitation],
ISBLANK(‘Case Study Database’[MoldCavitation]) && ‘Case Study Database’[MoldCavitation]<=0, DIVIDE(60,‘Case Study Database’[ConventionalCycleTime])*60,
DIVIDE(60,‘Case Study Database’[ConventionalCycleTime])*60 )))

// This formula calculates the average conventional throughput (in parts per hour). If mold cavitation is entered, it calculates parts per hour as (60/Cycle Time)60Mold Cavitation. If no mold cavitation entered it assumes cavitation of 1: (60/Cycle Time)*60.))))