topic Re: Calculate values based on contained strings in the data from column in DAX Commands and Tips

Calculate values based on contained strings in the data from column

kitti — Wed, 08 Jul 2020 04:10:19 GMT

Hi,

Please anyone advise how to calculate a value based on contained strings in the data from another column.

For instance:

Table1 has colA as text and colB as numeric; There are some data that should be calculated as percentage, so I want to * 100 if colA contains "LPercent" string value. Also, I want to add the symbol "%" after multiplying.

Thanks

Re: Calculate values based on contained strings in the data from column

deepak91g — Wed, 08 Jul 2020 09:24:44 GMT

Create a calculated column

TestColumn =
IF ( ColA = "LPercent", ( ColB * 100 ) & '%', BLANK () )

Re: Calculate values based on contained strings in the data from column

kitti — Thu, 09 Jul 2020 08:15:24 GMT

Hi,

How do I change "=" to something like CONTAINS function? And I want to leave the original ColB data instead of BLANK (). I already tried it but got an error.

TestColumn =
IF ( ColA = "LPercent", ( ColB * 100 ) & '%', BLANK () )

Thanks

Re: Calculate values based on contained strings in the data from column

deepak91g — Thu, 09 Jul 2020 08:45:31 GMT

You can try creating a custom column in Power Query instead of creating a DAX measure.

https://docs.microsoft.com/en-us/powerquery-m/text-contains

Yes, you can use ColB instead of Blank() but need ensure same data type for all values

Re: Calculate values based on contained strings in the data from column

Anonymous — Thu, 09 Jul 2020 08:49:12 GMT

Hi kitti ,

You can try this Calcuated Column

Column = IF ( CONTAINSSTRING ( Table['ColA'], "LPercent" ), ( 'Table'[ColB] * 100 ) & '%', 'Table'[ColB] )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

Re: Calculate values based on contained strings in the data from column

kitti — Mon, 13 Jul 2020 07:26:49 GMT

I have just tried but got an error, please help

Thanks,

Re: Calculate values based on contained strings in the data from column

Anonymous — Mon, 13 Jul 2020 08:36:34 GMT

Hi kitti ,

Try this

Column = IF ( CONTAINSSTRING ( Sheet1[PCQ Item], "LPercent" ), ( Sheet1[Result- QTD] * 100 ) & "%", CONVERT ( Sheet1[Result- QTD], STRING ) )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

Re: Calculate values based on contained strings in the data from column

kitti — Tue, 14 Jul 2020 08:17:15 GMT

Anonymous

Thanks a bunch. It works perfectly

Regards,