Convert text with percentage symbol to number (DAX)

[molegris]

Hi,

I need to reuse a measure that has numbers displayed as text.  So, I'm trying to convert the output back into decimal but I keep getting errors.  I've tried VALUE() and CONVERT() without any luck.

Basically, I need to do something like this:

VAR __stringA = "20.50$"
VAR __stringB = "10%"
RETURN VALUE(stringA) * VALUE(stringB)

 With the constraint that strings A and B are outputs from a measure that I'm not allowed to modify.

Thanks

--mo

[AlexisOlson · Accepted solution]

You could strip off the rightmost character and divide by 100 for the percentage.

Convert = 
VAR __stringA = "20.50$"
VAR __stringB = "10%"
RETURN
    VALUE ( LEFT ( __stringA, LEN ( __stringA ) - 1 ) ) *
    VALUE ( LEFT ( __stringB, LEN ( __stringB ) - 1 ) ) / 100

[AlexisOlson · Accepted solution]

You could strip off the rightmost character and divide by 100 for the percentage.

Convert = 
VAR __stringA = "20.50$"
VAR __stringB = "10%"
RETURN
    VALUE ( LEFT ( __stringA, LEN ( __stringA ) - 1 ) ) *
    VALUE ( LEFT ( __stringB, LEN ( __stringB ) - 1 ) ) / 100

[molegris]

Hi,

Thanks for the reply.  

Since I cannot predict what is going to be the nature of string A and B, I cannot devide by 100 all the time.

To give you a better idea, here's the measure from which strings A and B are from :

NomIndicateur.Valeur = 
VAR __valeur = SWITCH(
    SELECTEDVALUE(_liste_indicateur_relance[Code]),
    "PersonnesVisees.N", [PersonnesVisees.Nbre],
    "Reponse.N", [Reponse.Nbre],
    "Reponse.T", [Reponse.Taux],
    "Age.Moy", [Age.Moy],
    "Emploi.T",[Emploi.Taux],
    "Etudes.T", [Etudes.Taux],
    "Recherche.T", [Recherche.Taux],
    "Inactif.T", [Inactif.Taux],
    "Chomage.T", [Chomage.Taux],
    "EtudesLiees.T", [EtudesLiees.TauxRelatif],
    "EmploiLie.T", [EmploiLie.TauxRelatif],
    "EmploiTempsPlein.T", [EmploiTempsPlein.TauxRelatif],
    "EmploiPermanent.T", [EmploiPermanent.TauxRelatif],
    "SentimentCompetenceEleveEmploi.T", [SentimentCompetenceEleveEmploi.TauxRelatif],
    "SalaireHoraire.Moy", [SalaireHoraire.Moy],
    BLANK()
)

RETURN IF( NOT ISBLANK(__valeur),
    SWITCH(
        SELECTEDVALUE(_liste_indicateur_relance[Format]),
        "Entier", FORMAT(__valeur,"0"),
        "Décimal", FORMAT(__valeur,"0.0"),
        "Pourcentage", FORMAT(__valeur,"0%"),
        "Monnaie", FORMAT(__valeur, "0.00$"),
        __valeur
    )
)

Well, I guess I can test the right-most character and take the appropiate action based on if it's % or $.  It looks like a patch to me though.  I would've liked a more robust version of VALUE() in which I don't have to worry so much about the exact format the other analysts used, are using, and will eventually use.

Your suggestion works and I will most likely end up using (thank you) but I can already foresee the day my application will crash because a client asked to change the display to "$20.50" instead of "20.50$".  See what I mean?

--mo

[AlexisOlson · Accepted solution]

What I've done when I have this sort of situation is to split the measure into two pieces: one for the measure switching and one for the format switching.

For example:

NomIndicateur.Valeur = 
SWITCH(
    SELECTEDVALUE(_liste_indicateur_relance[Code]),
    "PersonnesVisees.N", [PersonnesVisees.Nbre],
    "Reponse.N", [Reponse.Nbre],
    "Reponse.T", [Reponse.Taux],
    "Age.Moy", [Age.Moy],
    "Emploi.T",[Emploi.Taux],
    "Etudes.T", [Etudes.Taux],
    "Recherche.T", [Recherche.Taux],
    "Inactif.T", [Inactif.Taux],
    "Chomage.T", [Chomage.Taux],
    "EtudesLiees.T", [EtudesLiees.TauxRelatif],
    "EmploiLie.T", [EmploiLie.TauxRelatif],
    "EmploiTempsPlein.T", [EmploiTempsPlein.TauxRelatif],
    "EmploiPermanent.T", [EmploiPermanent.TauxRelatif],
    "SentimentCompetenceEleveEmploi.T", [SentimentCompetenceEleveEmploi.TauxRelatif],
    "SalaireHoraire.Moy", [SalaireHoraire.Moy],
    BLANK()
)

and

NomIndicateur.Valeur.Formatted = 
VAR __valeur = [NomIndicateur.Valeur]
RETURN IF( NOT ISBLANK(__valeur),
    SWITCH(
        SELECTEDVALUE(_liste_indicateur_relance[Format]),
        "Entier", FORMAT(__valeur,"0"),
        "Décimal", FORMAT(__valeur,"0.0"),
        "Pourcentage", FORMAT(__valeur,"0%"),
        "Monnaie", FORMAT(__valeur, "0.00$"),
        __valeur
    )
)

Reference the unformatted one for your multiplication measure.

[molegris]

Hi @AlexisOlson ,

THAT I like! 🙂 

I don't "own" that measure and I'm not sure the team will be able to modify it because it might impact other analysts and content creators BUT I'll make sure your recommandation is used for future development as a best practice !

Thank you!

--mo

[AlexisOlson]

They can keep it from causing trouble for others by defining the unformatted version measure with a new name rather than the names I had suggested. That is, instead of NomIndicateur.Valeur and NomIndicateur.Valeur.Formatted, you could use NomIndicateur.Valeur.Numeric and NomIndicateur.Valeur.