cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?

Anonymous
Not applicable

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.

Anonymous
Not applicable

Hi @surajde ,

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors