Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating days active cases

Hi,

I'm kinda new to DAX and I'm experiencing some weird behaviour on a calculation
I'm trying to count how many days a given ticket number (customer case) is active until today.

 

Here is the result:

The ticket number (CAS-24075 where the CRM unit is owner of) is open for 641 days, but for some reason he is calculating something for the other business units.
I would expect that for that ticket only 1 row gets returned with the result of 641 days.

Here is my DAX formula:

 

I would expect a result as follows where the not owning business units have blank results:

 

What am I doing wrong?

Regards

 

K.

  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

     

    You can try something like below.

    Oldest Active Ticket = 
VAR _active = 
    CALCULATETABLE(
        ADDCOLUMNS( 
            SUMMARIZE(
                Incident, 
                Incident[Ticket Number],
                Incident[Unit Name]
            ),
            "start", 
                CALCULATE(
                    COUNTROWS( dates ),
                    DATESBETWEEN( dates[Date], MIN( Incident[CreatedOn_DateKey] ), TODAY() ),
                    dates[isWeekend] = FALSE()
                )
        ),
        KEEPFILTERS( 'Incident'[State Code] = "Active" )
    )
RETURN
SUMX( _active, [start] )
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

1 Reply

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

     

    You can try something like below.

    Oldest Active Ticket = 
VAR _active = 
    CALCULATETABLE(
        ADDCOLUMNS( 
            SUMMARIZE(
                Incident, 
                Incident[Ticket Number],
                Incident[Unit Name]
            ),
            "start", 
                CALCULATE(
                    COUNTROWS( dates ),
                    DATESBETWEEN( dates[Date], MIN( Incident[CreatedOn_DateKey] ), TODAY() ),
                    dates[isWeekend] = FALSE()
                )
        ),
        KEEPFILTERS( 'Incident'[State Code] = "Active" )
    )
RETURN
SUMX( _active, [start] )
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski