Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SWITCH ( TRUE() ) with multiple criteria?

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

amountthreshold
jimapples.10<20
jimapples.09>=21 & < 40
jimapples.08>= 41 & < 60
jimapples.07>61
jimoranges.10<20
jimoranges.09>=21 & < 40
jimoranges.08>= 41 & < 60
jimoranges.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?
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    @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>
)

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    @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>
)
    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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. 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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
    )