If column value on a row contains x then do y else do z

[FatherTheWizard]

Hi,

I have a table where I have the below info:

Date	Employee	Employment type	FTE hours
1.1.2019	1	Hourly	160
1.1.2019	2	Fixed	160
1.1.2019	3	Fixed	160

- I would like to have DAX where it SUMs [FTE hours] for [Employment type] = "Fixed" and uses Measure X for [Employment type] ="Hourly"

I have tried IF CONTAINS but I guess it checks and decides TRUE or FALSE for the whole column and I would like the TRUE/FALSE test on each row.

[yelsherif]

You can create a measure like:

_measure = if(selectedvalue(Table1[Employment Type])="Fixed", calculate(sum(Table1[FTE hours]), ALLSELECTED(Table1)), [Measure X])

[FatherTheWizard]

Thanks @yelsherif , I got closer.

I think we cannot use ALLSELECTED here because the monthly figures are now shown in a wrong way between different months?

Without ALLSELECTED part of the formula the results were close but I noticed that when I individually filteres Hourly workers I got the correct answer for those employees but when I filtered both Hourly and Fixed workers it calculated according to Fixed workers way and the total was not correct (=the hourly worker part of the formula was not applied anymore).

[FatherTheWizard]

@yelsherif 

If I elaborate the use case a bit.

As slicer filters on the report page I am using for example time (months) and also employment type (hourly, fixed). This way users can observe results based on time and based on employment types. Your current suggestion unfortunately did not work through time and when selecting only one of the employment types, I got different results compared to when selecting both of them at the same time (separate sums were not the sum I got when selecting both from slicer).

[v-lili6-msft · Accepted solution]

hi, @FatherTheWizard 

This looks like a measure totals problem. Very common. See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

and for your case, you try this formula based on the _measure

Result = 
var _table=SUMMARIZE(Table1,Table1[Employee],Table1[Employment type],"_total",[_measure]) return
SUMX(_table,[_total])

Result:

Best Regards,

Lin