Forum Discussion
Calculating maxifs
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 |
3 Replies
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 _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Do I need to create index column?
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 _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
