SWITCH ( TRUE() ) with multiple criteria?

[Anonymous]

I'm trying to perform a calculation that will render costs charged by multiple vendors across multiple products and then get a grand total. Maybe I'm trying to do too much up front, but here's where I'm at so far. Table A is Vendor, and I'm trying to do calculations either in a statement or another table. Sorry for the mess in advance. Trying to figure out how to make it all work. 

Vendor A charges 4 different amounts based on threshold: 

Vendor	product	amount	threshold
jim	apples	.10	<20
jim	apples	.09	>=21 & < 40
jim	apples	.08	>= 41 & < 60
jim	apples	.07	>61
jim	oranges	.10	<20
jim	oranges	.09	>=21 & < 40
jim	oranges	.08	>= 41 & < 60
jim	oranges	.07	>61

There are 2 vendors that have different prices for the 2 products and 3 vendors that have a combined rate. All vendors have different thresholds and prices. I've been trying to write a Switch ( True () ) measure to solve this, but am not having much luck. here's my attempt at getting this to work for a single vendor. After I've done that, I'll expand the statement to work for the rest. 

Vendor PPM =

VAR jimApples_300k = 0.4070

VAR JimOranges_300k = 0.3960

VAR JimApples_900k = 0.3060

VAR JimOranges_900k = 0.3100

return

SWITCH( TRUE(),

CALCULATE('Vendor'[Vendor Billable] >= 300000,

'vendor'[VENDORNAME] = "jim",

'Vendor '[VENDORNAME] ="jim"),

'Vendor '[Vendor Billable]*jimApples_300k,

'Vendor '[Vendor Billable] >300000 && 'Vendor'[Vendor Billable] <=900000, 'Vendor'[Vendor Billable] * jimApple_900k,

'Vendor Call Data'[Vendor Billable]

First, will switch(true()) work? Second, if not, does anyone have another idea of how to set this up?

[amitchandak]

@Anonymous , Switch true will work but i doubt syntax you use refer example

Switch([age_group]
, "AgeGRoup_0-4" , 1
, "AgeGroup_5-9" , 2
, "AgegRoup_10-14" ,3
, "AgegRoup_15-19" , 4
, "AgegRoup_20-24" , 5
, "AgegRoup_25-29" , 6

7
)

or

Switch([age_group]
, "AgeGRoup_0-4" && [ABC] =4 || [def] =4 , 1
, "AgeGroup_5-9" , 2
, "AgegRoup_10-14" ,3
, "AgegRoup_15-19" , 4
, "AgegRoup_20-24" , 5
, "AgegRoup_25-29" , 6

7
)

[Greg_Deckler · Accepted solution]

@aharon0414 - I usually do not see calculate statements in a SWITCH(TRUE()...) statement. SWITCH is usually like:

SWITCH(TRUE(),
  'Vendor'[VENDORNAME] = "jim" && 'Vendor'[Vendor Billable] > 300000, <some calc>,
  'Vendor'[VENDORNAME] = "jim" && 'Vendor'[Vendor Billable] > 150000, <some calc>,
  <some calc>
)

[Anonymous]

Thank you @Greg_Deckler !

That seemed to get me a step forward. SWITCH (True) ) is working now, but in my variables, it will only calculate based on the first variables created. My variables all reference another variable that transitions the context between respective vendors, but that is not carrying forward in the totality of the SWITCH statement. I'll try to work up a mockup to share, but for now, my data is confidential and it may be a lot of work to put something together. 

[parry2k]

@Anonymous it can be easily achieved by measures, if you put sample data in pbix file, it will help and I don't think SWITCH is the answer here, I could be totally wrong.