Forum Discussion
Search a String for 4 different variables then do a calculation based upon results
I am trying to search a column of product with multiple values for the first two letters of that column then do a calculation:
Product Code Column has values:
AR BLK
AR BLKMB
AR UHP
OX UHP
OX USP
NI NLK
NI FGBLK
etc.
Then if the Product Code = "AR" multiply that by a value
If the Product Code = "NI" multiply that by a different value
If the Product Code = OX" multiply that by a different value (Quantity / # Trips)
Here's where I'm failing, I believe I need to use the LEFT function; but it failed as well:
you want to create a measure, correct?
TPT = IF ( HASONEVALUE ( 'DeliveryPoint'[ProductCode] ), SWITCH ( LEFT ( FIRSTNONBLANK ( 'DeliveryPoint'[ProductCode], TRUE () ), 2 ), "NI", DIVIDE ( SUM ( 'Shifts and Events'[DeliveredQuantity(DU)] ) * 0.0000362318840580, [Trips(#)] ), "AR", DIVIDE ( SUM ( 'Shifts and Events'[DeliveredQuantity(DU)] ) * 0.0000517063081696, [Trips(#)] ), "OX", DIVIDE ( SUM ( 'Shifts and Events'[DeliveredQuantity(DU)] ) * 0.0000413907284768, [Trips(#)] ), "CO", DIVIDE (
SUM ( 'Shifts and Events'[DeliveredQuantity(DU)] ) / 2000, [Trips(#)] ) ) )For a calculated column this code could be simplified
// Please do yourself a favour and // first create a measure (can be hidden): [DU Total Code-Agnostic] = SUM ( 'Shifts and Events'[DeliveredQuantity(DU)] ) // Then create a table that will map // the 2-letter codes into factors // (the numbers 0.0000362318840580...). // This could be best done in Power Query. // You can call the table CodeToFactorMapping. // Then create measures (as many of them
// as the number of 2-letter codes, where
// xx stands for such a code, NI, OX...): [DU Total xx] = var __factor = <get the factor from the mapping table for code xx using lookupvalue> return [DU Total Code-Agnostic] * __factor // Your final measure would be something // like this: TPT = var __productCode = SELECTEDVALUE ( 'DeliveryPoint'[ProductCode] ) var __2letterCode = LEFT ( __productCode, 2 ) var __total = SWITCH ( __2letterCode, "NI", [DU Total NI], "AR", [DU Total AR], "OX", [DU Total OX], "CO", [DU Total CO] ) var __trips = [Trips(#)] var __tpt = divide ( __total, __trips ) return __tptPlease always try to keep your code clean. If there's something that repeats itself, make it into a unit on its own. You'll save yourself time and frustration and when a time comes to change, you'll need to make the change in one place only.
Best
Darek
2 Replies
you want to create a measure, correct?
TPT = IF ( HASONEVALUE ( 'DeliveryPoint'[ProductCode] ), SWITCH ( LEFT ( FIRSTNONBLANK ( 'DeliveryPoint'[ProductCode], TRUE () ), 2 ), "NI", DIVIDE ( SUM ( 'Shifts and Events'[DeliveredQuantity(DU)] ) * 0.0000362318840580, [Trips(#)] ), "AR", DIVIDE ( SUM ( 'Shifts and Events'[DeliveredQuantity(DU)] ) * 0.0000517063081696, [Trips(#)] ), "OX", DIVIDE ( SUM ( 'Shifts and Events'[DeliveredQuantity(DU)] ) * 0.0000413907284768, [Trips(#)] ), "CO", DIVIDE (
SUM ( 'Shifts and Events'[DeliveredQuantity(DU)] ) / 2000, [Trips(#)] ) ) )For a calculated column this code could be simplified
// Please do yourself a favour and // first create a measure (can be hidden): [DU Total Code-Agnostic] = SUM ( 'Shifts and Events'[DeliveredQuantity(DU)] ) // Then create a table that will map // the 2-letter codes into factors // (the numbers 0.0000362318840580...). // This could be best done in Power Query. // You can call the table CodeToFactorMapping. // Then create measures (as many of them
// as the number of 2-letter codes, where
// xx stands for such a code, NI, OX...): [DU Total xx] = var __factor = <get the factor from the mapping table for code xx using lookupvalue> return [DU Total Code-Agnostic] * __factor // Your final measure would be something // like this: TPT = var __productCode = SELECTEDVALUE ( 'DeliveryPoint'[ProductCode] ) var __2letterCode = LEFT ( __productCode, 2 ) var __total = SWITCH ( __2letterCode, "NI", [DU Total NI], "AR", [DU Total AR], "OX", [DU Total OX], "CO", [DU Total CO] ) var __trips = [Trips(#)] var __tpt = divide ( __total, __trips ) return __tptPlease always try to keep your code clean. If there's something that repeats itself, make it into a unit on its own. You'll save yourself time and frustration and when a time comes to change, you'll need to make the change in one place only.
Best
Darek