Switch function not working as expected

Power BI
1

I am having trouble doing something that has got to be simple and I’m just over looking how to do it.

I’m trying to create a measure that will look at a column and if it see’s the value “CTMPM” the return value of 0. Currently I have 3 measures that I add together to get a total cost.

Total Service Sales = [Labor Charge] + [Misc Charge] + [Parts Charge].

I need to set the Total Service Charge to be 0 if CTMPM is found. This would happen if in the ‘Service Ledger Entries’[Service Order Type] had a value of “CTMPM”

image
image995×403 51.2 KB

Labor Charge Measure
Labor Charge =
Var laborcharge = CALCULATE(SUM(‘Service Ledger Entries’[Amount]) * -1, FILTER(‘Service Ledger Entries’,‘Service Ledger Entries’[Resource Type] = “Resource” ))
return laborcharge

Misc Charge Measure
Misc Charge = CALCULATE(SUM(‘Service Ledger Entries’[Amount]) * -1, FILTER(‘Service Ledger Entries’,‘Service Ledger Entries’[Resource Type] = “Service Cost”))

Parts Charge Measure
Parts Charge = CALCULATE(SUM(‘Service Ledger Entries’[Amount]) * -1, FILTER(‘Service Ledger Entries’,‘Service Ledger Entries’[Resource Type] = “Item”))

Total Service Charge Measure
Total Service Sales = [Labor Charge] + [Parts Charge] + [Misc Charge]

Approach
Test measure = SWITCH(SELECTEDVALUE
Test measure = SWITCH(SELECTEDVALUE(‘Service Ledger Entries’[Service Order Type]), “CTMPM”,0,‘Customer Service Charge Measures’[Total Service Sales])

When I add this measure to the visual, I get no column, no error.

Customer Fleet Report - Sales Team.pbix (17.4 MB)

2

@lomorris SELECTEDVALUE will return Blank because there are multiple values in the filter context, try to use COUNTROWS ( DISTINCT () ) or ISEMPTY ( DISTINCT () ) construct to check if the value exists in the filter context.

3

Hi @lomorris
My initial quick response without digging right into your PBIX is

Test measure =
SWITCH(
    Max(‘ Service Ledger Entries ’ [Service Order Type] ),
    “ CTMPM ”,
    0,
    ‘ Customer Service Charge Measures ’ [Total Service Sales]
)

But I may be wrong. Give it a whirl and let me know. If it doesn’t work I’ll go a bit further for you.
Let me know
Pete

4

Thanks Pete, but did not fix it.

5

Could you expand on your suggestion. Are saying I should use Countrows(Distinct()) along with Switch?. Do you have an quick example for guidance?

6

Just dropped your PBIX down.
No idea why MAX doesn’t work but SELECTEDVALUE wasn’t the thing.

Got it to work with this:

Test Measure =
CALCULATE(
    [Total Service Sales],
  'Service Ledger Entries'[Service Order Type] <> "CTMPM"
)

It will report blank rather than 0 in the CTMPM rows. If you need 0s then an if(ISBLANK() etc will fix)

Love the way you’ve got the date options appearing in on slicer btw.

PBIX attached
Customer Fleet Report - Sales Team.pbix (17.4 MB)

Thanks
Pete

7

Pete, late last night I came up with a solution that works, but in my opinion is not very elegant at all. I just saw your response and it seemed to me to be a much better and simpler approach to what I put together. Unfortunately it had a side effect that still didn’t solve my challenge. When I applied your solution, as you said, it showed blank for CTMPM rows. I replaced my [Total Service Sales] measure with the test measure containing your solution and it dropped CTMPM rows altogether which to me makes sense based on the <> “CTMPM”. Below is what I came up with yesterday. As I said, not very elegant but it works.

Ultimately what this does is zero out for a row that has CTMPM

Total Service Sales = [Laborsumx Charge] + [Partssumx Charge] + [Miscsumx Charge] + ([Labor Charge] - [Labor Charge]) + ([Misc Charge] - [Misc Charge]) + ([Parts Charge] - [Parts Charge])

Laborsumx Charge = SUMX(FILTER(‘Service Ledger Entries’,‘Service Ledger Entries’[Service Order Type] <> “CTMPM” && ‘Service Ledger Entries’[Resource Type] = “Resource”), ‘Service Ledger Entries’[Amount] * -1
)

Miscsumx Charge = SUMX(FILTER(‘Service Ledger Entries’,‘Service Ledger Entries’[Service Order Type] <> “CTMPM” && ‘Service Ledger Entries’[Resource Type] = “Service Cost”), ‘Service Ledger Entries’[Amount] * -1
)

Partssumx Charge = SUMX(FILTER(‘Service Ledger Entries’,‘Service Ledger Entries’[Service Order Type] <> “CTMPM” && ‘Service Ledger Entries’[Resource Type] = “Item”), ‘Service Ledger Entries’[Amount] * -1
)

I have attached my pbix file with the solution I implemented. I am sure there is a much better way to do what I have done.
Customer Fleet Report - Sales Team.pbix (17.5 MB)

Also, thanks for the compliment on my date slicer.

1 Like
8

Hi Larry
I got this one worked out I think, but stuck on something else today.
Should respond tomorrow

Pete

1 Like
9

Hi Larry
Give this one a go

Total Service Sales =
VAR test2 = CALCULATE(
    sum( 'Service Ledger Entries'[Amount] ),
  'Service Ledger Entries'[Service Order Type] <> "CTMPM" &&
 'Service Ledger Entries'[Resource Type] IN { "Resource", "Service Cost", "Item" }
) * -1

Return if( ISBLANK( test2 ), 0, test2 )

I’ve done a couple of things

  1. Used a sum rather than a sumX. This will run quicker in a large dataset because won’t need to look at every row.
  2. Brought your resource types all into one filter with IN
  3. Managed the blank thing with an ISBLANK construct

Cheers
Pete

1 Like
10

Hello @lomorris, just following up if the response from @BINavPete help you solve your inquiry?

We’ve noticed that no response has been received from you since November 10. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

11

Pete,
Thanks for the solution, it works as expected. I did notice that when certain fields are added to the table visual, the solution does not work. I don’t believe it’s your solution as much as it is my data model. I consider this solved. Thanks so much for your effort to help me.

2 Likes