topic Re: How To Get Multiple Min and Max Occurences in Dax in DAX Commands and Tips

How To Get Multiple Min and Max Occurences in Dax

casabs — Wed, 06 Mar 2024 08:11:30 GMT

I am trying to return periods of holiday for some colleagues in my organisation but can't find a way in Dax to achive this. Equally if it can be acheived in SQL, that is how the data is currently stored

Desired Result:

Colleague 123: Blank/No Holiday

Colleague 456: 02/02/2024 - 03/02/2024

Colleague 789: 02/02/2024 - 03/02/2024

06/02/2024 - 08/02/2024

The table is currently vertically strucutured with a combined Key of colleague ID and Date

Example below:

Date	Colleague ID	Holiday
01/02/2024	123	0
02/02/2024	123	0
03/02/2024	123	0
04/02/2024	123	0
05/02/2024	123	0
06/02/2024	123	0
07/02/2024	123	0
08/02/2024	123	0
01/02/2024	456	0
02/02/2024	456	7
03/02/2024	456	7
04/02/2024	456	0
05/02/2024	456	0
06/02/2024	456	0
07/02/2024	456	0
08/02/2024	456	0
01/02/2024	789	0
02/02/2024	789	7
03/02/2024	789	7
04/02/2024	789	0
05/02/2024	789	0
06/02/2024	789	7
07/02/2024	789	7
08/02/2024	789	7

Re: How To Get Multiple Min and Max Occurences in Dax

rbriga — Wed, 06 Mar 2024 10:16:14 GMT

Let's solve this in SQL- like you've hinted, it's best to push the calculations to the source.

I'm assuming you have a date table [DATE_TABLE] with the date field [DATE_VALUE].

Also, you have the table [VACATION_TABLE] with VACATION_START and VACATION_END.

Now:

SELECT VACATION.Colleague_ID, DATES.DATE_VALUE, '1' AS "Vacation Day" FROM VACATION_TABLE VACATION INNER JOIN DATE_TABLE DATES ON DATES.DATE_VALUE >= DATE(VACATION.VACATION_START) AND DATES.DATE_VALUE <=DATE(VACATION.VACATION_END)

(If VACATION_START and VACATION_END are already dates, you don't need DATE() )

So if colleague 123 was out between 2024-01-04 and 2024-01-11, this will return:

ID DATE_VALUE Vacation Day

123	2024-01-04	1
123	2024-01-05	1
123	2024-01-06	1
123	2024-01-07	1
123	2024-01-08	1
123	2024-01-09	1
123	2024-01-10	1
123	2024-01-11	1

Does this work?

Re: How To Get Multiple Min and Max Occurences in Dax

casabs — Wed, 06 Mar 2024 11:57:25 GMT

Hey thanks for the reply

I don't have the vacation start and end date in that format, and that is part of the challenge. The table has a date column, and a vacation column.

In the vacation column the value is 0 for days with no vacation and then a positive integer (representing number of hours) for days when holiday is booked.

the problem with min(date) and max(date) where holiday > 0 will only give me one date for min and max, not each occurence

Re: How To Get Multiple Min and Max Occurences in Dax

rbriga — Thu, 07 Mar 2024 10:33:47 GMT

Alright, I mistook the direction you need to go.

Let's try more SQL, based on this Stack Overflow post.

CASE HOLIDAY WHEN 0 THEN NULL ELSE MAX(CASE WHEN HOLIDAY = 0 THEN DATEADD(DAY,1,DATE) END) OVER (PARTITION BY COLLEAGUE_ID ORDER BY DATE by date) END as VACATION START, CASE HOLIDAY WHEN 0 THEN NULL ELSE MIN(CASE WHEN HOLIDAY = 0 THEN DATEADD(DAY,-1,DATE) END) OVER (PARTITION BY COLLEAGUE_ID ORDER BY DATE by date DESC) END as VACATION END,

Does that create the desired start and end dates?

If so, you can then use that as a temporary table (WITH T AS) and SELECT DISTINCT