Forum Discussion

Oros's avatar
Oros
Post Prodigy
2 years ago
Solved

Create a new column, extract

Hello,   I have a payment terms column. How do I create a column based on the payment terms column to show the percentage if available, if no percentage available, then put zero as the value.  Than...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from vicky_ , please allow me to provide another insight:

     

    Hi Oros ,

     

    If you want to achieve the result with calculated column, you can try below formula and change [result] column type by whole number.

    result = 
VAR Terms = 'PaymentTerms'[TERMS]
VAR PercentPosition =
    SEARCH ( "%", Terms, 1, LEN ( Terms ) )
VAR SpacePosition =
    SEARCH ( "S", Terms, 1, LEN ( Terms ) )
RETURN
    IF (
        CONTAINSSTRING ( PaymentTerms[TERMS], "%" ),
        MID (
            PaymentTerms[TERMS],
            SpacePosition + 1,
            PercentPosition - SpacePosition - 1
        ),
        "0"
    )

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

Oros's avatar
Oros
Post Prodigy
2 years ago

Hi Anonymous ,

 

Thank you for for help.  It looks fine but just getting that small error that I could not figure out.  Thanks again.

 

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Oros ,

 

Based on the description of the error message, please check the data column classes of the columns referenced in the mid function to make sure they are all of type text.

 

related document:

MID function (DAX) - DAX | Microsoft Learn

 

Best Regards,
Adamk Kong

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.