topic Re: Calculate between two columns of hour min and max associated with an hour interval dimension in DAX Commands and Tips

Calculate between two columns of hour min and max associated with an hour interval dimension

Anonymous — Sat, 25 Apr 2020 12:37:38 GMT

I need to do a "count if" in the power bi. I have an hour min and max of the agent on the day, I would like to know the representativeness of that time per hour.

For this I have a dimension of hours, min and seconds. But I can't calculate it because DAX doesn't allow conditional sum fuction.

Formula used in excel:
= CONTESES (C: C; "<=" & G2; 😧 D; "> =" & G2)

MIN HOUR | MAX HOUR

07:55:00 | 18:49:00

REPRESENTATIVITY | RESULT

00:00:00 0
01:00:00 0
02:00:00 0
03:00:00 0
04:00:00 0
05:00:00 0
06:00:00 0
07:00:00 0
08:00:00 1
09:00:00 1
10:00:00 1
11:00:00 1
12:00:00 1
13:00:00 1
14:00:00 1
15:00:00 1
16:00:00 1
17:00:00 1
18:00:00 1
19:00:00 0
20:00:00 0
21:00:00 0
22:00:00 0
23:00:00 0

Re: Calculate between two columns of hour min and max associated with an hour interval dimension

Mariusz — Sat, 25 Apr 2020 13:38:13 GMT

Hi Anonymous

If you are trying to count hours between start and end then you can use DATEDIFF function
https://docs.microsoft.com/en-us/dax/datediff-function-dax

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Calculate between two columns of hour min and max associated with an hour interval dimension

Anonymous — Sat, 25 Apr 2020 14:21:13 GMT

Mariusz I'm sorry, maybe I didn't express it correctly, I'm sure you will know the answer.

I need to replicate the table on the right of excel (print 1) for power bi (print 2), but I can't get the quantity separated per hour as I did in the excel formula.

please let me know if i need to send more information

Re: Calculate between two columns of hour min and max associated with an hour interval dimension

Mariusz — Sat, 25 Apr 2020 14:39:01 GMT

Hi Anonymous

Sure, try the solution on the attached.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Calculate between two columns of hour min and max associated with an hour interval dimension

Anonymous — Sat, 25 Apr 2020 15:20:15 GMT

Mariusz worked perfectly. Thank you 😅

Re: Calculate between two columns of hour min and max associated with an hour interval dimension

Anonymous — Sat, 25 Apr 2020 16:12:25 GMT

Mariusz Just one more doubt. It would be possible to do this same scheme but in a measurement format? Calculating as a column I am unable to filter attributes of these agents such as teams, etc. that are in other tables.

Re: Calculate between two columns of hour min and max associated with an hour interval dimension

Mariusz — Sun, 26 Apr 2020 13:49:46 GMT

Hi Anonymous

Sure, the measure would be something like.

Measure = COUNTROWS( GENERATE( Hours, VAR __time = Hours[Time] RETURN FILTER( 'Table', 'Table'[MIN HOUR] <= __time && 'Table'[MAX HOUR] >= __time ) ) )

Just a small note, this sort of calculations tend to be quite slow, so you could create a factless fact table ( in the file you will find DAX Table "factless" but it should be done in Power Query preferably ) and just count the rows, please see the attached for an example.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn