Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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
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