Skip to main content
cancel
Showing results for 
Search instead 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

Reply
surajde
Helper I
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_NUMTICKET_OPENEDACTIVITY_TYPETICKET_STATE1ACTIVITY_TIMESTART_TIMESTOP_TIMEDeferred Hours
112233445/2/2022 13:21Customer LogQueued5/2/2022 13:21NULL5/2/2022 13:210
112233445/2/2022 13:21Initial CreationQueued5/2/2022 13:215/2/2022 13:215/4/2022 13:570
112233445/2/2022 13:21Customer LogQueued5/4/2022 13:575/4/2022 13:575/6/2022 15:170
112233445/2/2022 13:21Customer LogQueued5/6/2022 15:175/6/2022 15:175/6/2022 19:000
112233445/2/2022 13:21Combined LogQueued5/6/2022 19:005/6/2022 19:005/6/2022 19:020
112233445/2/2022 13:21State ChangeActive5/6/2022 19:025/6/2022 19:025/6/2022 19:020
112233445/2/2022 13:21Ticket TransferedActive5/6/2022 19:025/6/2022 19:025/6/2022 19:020
112233445/2/2022 13:21Work Queue UpdateActive5/6/2022 19:025/6/2022 19:025/7/2022 2:040
112233445/2/2022 13:21Owner ID Change  Active5/7/2022 2:045/7/2022 2:045/10/2022 17:000
112233445/2/2022 13:21Combined LogActive5/10/2022 17:005/10/2022 17:005/11/2022 10:410
112233445/2/2022 13:21Work Queue UpdateActive5/11/2022 10:415/11/2022 10:415/11/2022 10:410
112233445/2/2022 13:21Ticket TransferedActive5/11/2022 10:415/11/2022 10:415/11/2022 10:410
112233445/2/2022 13:21Owner ID Change  Active5/11/2022 10:415/11/2022 10:415/17/2022 12:520
112233445/2/2022 13:21Customer LogActive5/17/2022 12:525/17/2022 12:525/17/2022 18:000
112233445/2/2022 13:21Work Queue UpdateActive5/17/2022 18:005/17/2022 18:005/26/2022 4:050
112233445/2/2022 13:21Customer LogActive5/26/2022 4:055/26/2022 4:055/26/2022 4:050
112233445/2/2022 13:21State ChangeDeferred - Cust Information5/26/2022 4:055/26/2022 4:055/26/2022 11:437.619444444
112233445/2/2022 13:21Modify AllActive5/26/2022 11:435/26/2022 11:435/26/2022 11:430
112233445/2/2022 13:21State ChangeActive5/26/2022 11:435/26/2022 11:435/26/2022 11:440
112233445/2/2022 13:21Customer LogActive5/26/2022 11:445/26/2022 11:445/26/2022 11:440
112233445/2/2022 13:21State ChangeReady to Close5/26/2022 11:445/26/2022 11:445/26/2022 11:440
112233445/2/2022 13:21Modify AllReady to Close5/26/2022 11:445/26/2022 11:445/26/2022 11:440
112233445/2/2022 13:21Resolution Information ChangeReady to Close5/26/2022 11:445/26/2022 11:445/31/2022 11:450
112233445/2/2022 13:21State ChangeClosed5/31/2022 11:455/31/2022 11:455/31/2022 11:450
112233445/2/2022 13:21Customer LogReady to Close5/31/2022 11:455/31/2022 11:45NULL0
3 REPLIES 3
surajde
Helper I
Helper I

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 _ Rongtie

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

v-rongtiep-msft
Community Support
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
    )

vrongtiepmsft_0-1687748153610.png

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.

 

 

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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