Calculating maxifs

[surajde]

Hi Team, 

Please help calculating below MS Excel data using maxif funciton. Below is dummy data. I need to calculate result in Deferred Hours in PowerBI dashboard. Below is excel formula that worked perfect in Excel.

Need to replicate this one in PowerBI. 

Excel Formula: 

= IF( AND( F2<>"NULL", G2<>"NULL", LEFT(D2, 8)="Deferred", G2 >= MAX( B2, MAXIFS(E:E, A:A, A2, C:C, "Work Queue Update"))),(G2-MAX( F2, MAXIFS(E:E, A:A, A2, C:C, "Work Queue Update")))*24,0)

TICKET_NUM	TICKET_OPENED	ACTIVITY_TYPE	TICKET_STATE1	ACTIVITY_TIME	START_TIME	STOP_TIME	Deferred Hours
11223344	5/2/2022 13:21	Customer Log	Queued	5/2/2022 13:21	NULL	5/2/2022 13:21	0
11223344	5/2/2022 13:21	Initial Creation	Queued	5/2/2022 13:21	5/2/2022 13:21	5/4/2022 13:57	0
11223344	5/2/2022 13:21	Customer Log	Queued	5/4/2022 13:57	5/4/2022 13:57	5/6/2022 15:17	0
11223344	5/2/2022 13:21	Customer Log	Queued	5/6/2022 15:17	5/6/2022 15:17	5/6/2022 19:00	0
11223344	5/2/2022 13:21	Combined Log	Queued	5/6/2022 19:00	5/6/2022 19:00	5/6/2022 19:02	0
11223344	5/2/2022 13:21	State Change	Active	5/6/2022 19:02	5/6/2022 19:02	5/6/2022 19:02	0
11223344	5/2/2022 13:21	Ticket Transfered	Active	5/6/2022 19:02	5/6/2022 19:02	5/6/2022 19:02	0
11223344	5/2/2022 13:21	Work Queue Update	Active	5/6/2022 19:02	5/6/2022 19:02	5/7/2022 2:04	0
11223344	5/2/2022 13:21	Owner ID Change	Active	5/7/2022 2:04	5/7/2022 2:04	5/10/2022 17:00	0
11223344	5/2/2022 13:21	Combined Log	Active	5/10/2022 17:00	5/10/2022 17:00	5/11/2022 10:41	0
11223344	5/2/2022 13:21	Work Queue Update	Active	5/11/2022 10:41	5/11/2022 10:41	5/11/2022 10:41	0
11223344	5/2/2022 13:21	Ticket Transfered	Active	5/11/2022 10:41	5/11/2022 10:41	5/11/2022 10:41	0
11223344	5/2/2022 13:21	Owner ID Change	Active	5/11/2022 10:41	5/11/2022 10:41	5/17/2022 12:52	0
11223344	5/2/2022 13:21	Customer Log	Active	5/17/2022 12:52	5/17/2022 12:52	5/17/2022 18:00	0
11223344	5/2/2022 13:21	Work Queue Update	Active	5/17/2022 18:00	5/17/2022 18:00	5/26/2022 4:05	0
11223344	5/2/2022 13:21	Customer Log	Active	5/26/2022 4:05	5/26/2022 4:05	5/26/2022 4:05	0
11223344	5/2/2022 13:21	State Change	Deferred - Cust Information	5/26/2022 4:05	5/26/2022 4:05	5/26/2022 11:43	7.619444444
11223344	5/2/2022 13:21	Modify All	Active	5/26/2022 11:43	5/26/2022 11:43	5/26/2022 11:43	0
11223344	5/2/2022 13:21	State Change	Active	5/26/2022 11:43	5/26/2022 11:43	5/26/2022 11:44	0
11223344	5/2/2022 13:21	Customer Log	Active	5/26/2022 11:44	5/26/2022 11:44	5/26/2022 11:44	0
11223344	5/2/2022 13:21	State Change	Ready to Close	5/26/2022 11:44	5/26/2022 11:44	5/26/2022 11:44	0
11223344	5/2/2022 13:21	Modify All	Ready to Close	5/26/2022 11:44	5/26/2022 11:44	5/26/2022 11:44	0
11223344	5/2/2022 13:21	Resolution Information Change	Ready to Close	5/26/2022 11:44	5/26/2022 11:44	5/31/2022 11:45	0
11223344	5/2/2022 13:21	State Change	Closed	5/31/2022 11:45	5/31/2022 11:45	5/31/2022 11:45	0
11223344	5/2/2022 13:21	Customer Log	Ready to Close	5/31/2022 11:45	5/31/2022 11:45	NULL	0

[surajde]

Do I need to create index column? 

[Anonymous]

Hi @surajde ,

In the Excel formula, you seem to have a specified the cell. So you need to create an index column. You can create it in Power Query. Or you can refer to  the document: DAX 101: How to Create a Calculated Index Column in Power BI - Monocroft

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Anonymous]

Hi @surajde ,

Please have a try.

COLUMN =
VAR _A2 =
    CALCULATE ( MAX ( Table1[TICKET_NUM] ), FILTER ( Table1, Table1[index] = 2 ) )
VAR _MAXIFS =
    CALCULATE (
        MAX ( Table1[ACTIVITY_TIME] ),
        FILTER (
            Table1,
            Table1[TICKET_NUM] = _A2
                && Table1[ACTIVITY_TYPE] = "Work Queue Update"
        )
    )
VAR _1LEFT =
    LEFT ( Table1[TICKET_STATE1], 8 )
VAR _g2 =
    CALCULATE ( MAX ( Table1[STOP_TIME] ), FILTER ( Table1, Table1[index] = 2 ) )
VAR _B2 =
    CALCULATE (
        MAX ( Table1[TICKET_OPENED] ),
        FILTER ( Table1, Table1[index] = 2 )
    )
VAR _F2 =
    CALCULATE ( MAX ( Table1[START_TIME] ), FILTER ( Table1, Table1[index] = 2 ) )
VAR _BIG =
    IF ( _B2 > _MAXIFS, _B2, _MAXIFS )
VAR _BIG2 =
    IF ( _F2 > _MAXIFS, _F2, _MAXIFS )
RETURN
    IF (
        _F2 <> BLANK ()
            && _g2 <> BLANK ()
            && _1LEFT = "Deferred"
            && _g2 >= _BIG,
        ( _g2 - _BIG2 ) * 24,
        0
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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