Convert measure (dax) to column?

[Anonymous]

Hi, I am currently trying to create a column with an IF statement that returns back a 1 if true or 0 if false.

The table shows every time a user has taken a sick day in their period of working and each time is registered as a separate event, so for example, if someone called in sick for 2 days in a row, it still shows as two separate events. Also, this means the User ID comes up multiple times.

What the filter needs to do is to figure out whether a user has had 9 or more absences in a 4 month period, and if they have then for their ID mak down as 1 (true), if not then 0 (false).

There is a separate column for User ID, their start date, their end date, and a sick column then marks down as 1 or 0.5 per sickness leave.

It might be better to add up the sick days first in a column but I was not sure because it only matter if it is 9 or more absences in 4 months.

This is the Dax formula below which is the measure. (Sorry, I changed the column and table names as I am working with sensitive data which is why it is showing as grey but ignore that please)

I have included a table of sample data too for reference.

I would like this measure as a column so I can use it more efficiently. Thank you.

[Anonymous · Accepted solution]

Hi @Anonymous ,

[Absence Date] column is not inclued in your data sample.

And how about the "in 4 months" ? If it always based on the [Work End Date]- 4 months ?

I have tried the following formula

Column = 
var _minDate=DATEADD('Table (2)'[Work End Date].[Date],-4,MONTH)
return  CALCULATE(SUM('Table (2)'[Amount(Days)]), FILTER('Table (2)',[User ID]=EARLIER('Table (2)'[User ID]) && [Sickness Date]>=_minDate && [Sickness Date]<=[Work End Date]))

It will calculate the sum of Amount between:

	Date between
122	2016-6-5	2016-10-5
214	2018-2-4	2018-6-4
189	2021-8-14	2021-12-14

Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

Hi @Anonymous ,

Please provide your sample data in a copyable format.

I would recommend copying the example that you have shown, pasting into 'Enter Data' in Power Query, then copying the whole query using 'Advanced Editor' and pasting it into a code window here.

Pete