Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JasonGage
Regular Visitor

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:

 

TPT = if(HASONEVALUE('DeliveryPoint'[ProductCode]), SWITCH(VALUES('DeliveryPoint'[ProductCode]),"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(#)])))

 

 

2 ACCEPTED SOLUTIONS
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

Anonymous
Not applicable

// 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 __tpt

Please 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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

// 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 __tpt

Please 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

Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.