topic Convert Calculated Calculated Column to Measure in DAX Commands and Tips

Convert Calculated Calculated Column to Measure

andybamber — Thu, 25 Feb 2021 13:18:32 GMT

Hi group!

I'm stuck with something... I currently have a calculated column in power query like this:

if [TEAM.IS_ROLE] = 1 or Text.Contains([TEAM.RESOURCE_NAME],"Dummy") then [ALLOC_HOURS]/[DWH_CMN_PERIOD_M_V.PERIOD_FTE] else 0

i would like to turn this into a measure instead, but i cant figure out how to get the OR part correct

Can someone kindly advise! (or maybe in cant' be done?)

Cheers

Andy

Re: Convert Calculated Calculated Column to Measure

dedelman_clng — Thu, 25 Feb 2021 14:30:07 GMT

Hi andybamber -

Do you want this as a measure or as a calculated column in DAX? Since you appear to be referencing a field in a single row, I'm thinking Calculated Column.

The function you will want to replace Text.Contains is FIND() or SEARCH() . Therse functions returns an integer value instead of True/False, so your IF statement would be something like

IF ( OR( TEAM[IS_ROLE] = 1, FIND("Dummy", TEAM[RESOURCE_NAME], , 0) > 0 ), [ALLOC_HOURS]/[DWH_CMN_PERIOD_M_V.PERIOD_FTE] ,

0 )

You could also use the || method for OR

IF ( TEAM[IS_ROLE] = 1 || FIND("Dummy", TEAM[RESOURCE_NAME], , 0) > 0 , [ALLOC_HOURS]/[DWH_CMN_PERIOD_M_V.PERIOD_FTE] ,

0 )

Hope this helps

David

Re: Convert Calculated Calculated Column to Measure

andybamber — Thu, 25 Feb 2021 15:37:05 GMT

Hi David! it is currently a calculated column in a table, but i was hoping i might be able to create a measure instead, and then remove the calculated column...

Cheers

Andy

Re: Convert Calculated Calculated Column to Measure

dedelman_clng — Thu, 25 Feb 2021 15:41:32 GMT

Hi andybamber Andy - how will the measure be used and what is it calculating? When you are doing text searches or looking for specific values in a column, measures become tricky.

Can you share a copy of your pbix with sensitive data removed? OneDrive, Dropbox, google drive, etc. If not, sample data (inline, not a screen shot) and description/picture of your model would be helpful.

David