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

[Anonymous]

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

[Mariusz]

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

[Anonymous]

@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

[Mariusz · Accepted solution]

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

[Anonymous]

@Mariusz worked perfectly. Thank you 😅

[Anonymous]

@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.

[Mariusz]

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