cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## 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 3
Helper I

Do I need to create index column?

Community Support

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

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.

Community Support

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
)
``````

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors