Convert Calculated Calculated Column to Measure

[andybamber]

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

[dedelman_clng]

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

[andybamber]

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

[dedelman_clng]

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