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]/[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

Measures
OR function

3 Replies

  • 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