Dax to count if time = min & max time on second table

[cottrera]

Hi 

I have a data table (table 1) with names , max times and dates.  I have created a new table by entering data. This contains order, finish description, min time and max time.

Table 1

Name	Max Time	Appointment Date
Joshua	15:57:23	16/03/2021
Tony	15:57:25	25/03/2021
Troy	16:00:00	10/04/2021
David	17:00:23	20/04/2021
Derek	17:00:23	16/03/2021
Tony	17:30:42	26/03/2021
Mark	17:56:17	26/04/2021
Gary	17:56:31	01/04/2021
Paul	17:57:17	18/03/2021
Harry	17:57:57	31/03/2021
Gary	17:58:50	24/03/2021
Jake	18:00:00	16/03/2021
John	18:00:22	02/04/2021
Owen	18:00:35	23/03/2021
Ashley	19:55:38	16/04/2021
David	19:57:23	15/03/2021
David	19:57:42	25/03/2021
Christopher	19:57:43	09/04/2021

Table 2

Order	Finish	Min	Max	DAX Expected Result
1	Before 16:00	00:00:00	16:00:00	2
2	Between 16:00-17:00	16:00:00	17:00:00	1
3	Between 17:00-17:30	17:00:00	17:00:30	2
4	Between 17:30-18:00	17:30:00	18:00:00	6
5	Late finish / OHH after 18:00	18:00:00	00:00:00	7

I would like a DAX function to count if the times from table 1 fit the Min & max criteria of table 2

thank you

RIchard

[Jihwan_Kim · Accepted solution]

Hi, @cottrera 

Please correct me if I wrongly understood your question.

Please check the below picture and measure whether it is what you are looking for.

Result Measure =

COUNTROWS (

FILTER (

Table1,

Table1[Max Time] >= SELECTEDVALUE ( Table2[Min] )

&& Table1[Max Time] < SELECTEDVALUE ( Table2[Max] )

)

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[cottrera]

Hi thank you for your quick response. I added the Dax function to table 2. no errors but the result seems a little strange

I only received a result for one of the lines in table 2 and none of the other lines.

can you please advise. 

thanks 

RIchard

[amitchandak]

@cottrera , new column in table 2

countx(filter(table1, table1[Max Time] >= Table2[Min] && table1[Max Time] <= Table2[Max]),table1[Name])