Forum Discussion

andybamber's avatar
andybamber
Helper III
5 years ago

Convert Calculated Calculated Column to Measure

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]/[D...
Measures
OR function
dedelman_clng's avatar
dedelman_clng
Community Champion
5 years ago

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's avatar
    andybamber
    Helper III
    5 years ago

    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's avatar
      dedelman_clng
      Community Champion
      5 years ago

      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