cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
diskovered Member
Member

Help with dax

Can an expert in dax help me optimize / fix this code for Aging tickets. Thanks

 

Aging = 
IF (
    TODAY () - 'GetIssues'[created] <= 5
        && GetIssues[status.name] <> "Closed"
        && GetIssues[fixVersions.name] = BLANK ()
        || GetIssues[fixVersions.name] <> "Test",
    "0-5 days",
    IF (
        TODAY () + 5 - 'GetIssues'[created] <= 15
            && GetIssues[status.name] <> "Closed"
            && GetIssues[fixVersions.name] = BLANK ()
            || GetIssues[fixVersions.name] <> "Test",
        "6-15 days",
        IF (
            TODAY () + 15 - 'GetIssues'[created] <= 30
                && GetIssues[status.name] <> "Closed"
                && GetIssues[fixVersions.name] = BLANK ()
                || GetIssues[fixVersions.name] <> "Test",
            "16-30 days",
            IF (
                TODAY () + 30 - 'GetIssues'[created] <= 60
                    && GetIssues[status.name] <> "Closed"
                    && GetIssues[fixVersions.name] = BLANK ()
                    || GetIssues[fixVersions.name] <> "Test",
                "31-60 days",
                IF (
                    TODAY () + 60 - 'GetIssues'[created] <= 90
                        && GetIssues[status.name] <> "Closed"
                        && GetIssues[fixVersions.name] = BLANK ()
                        || GetIssues[fixVersions.name] <> "Test",
                    "61-90 days",
                    "+90 days"
                )
            )
        )
    )
)
1 ACCEPTED SOLUTION

Accepted Solutions
Cmcmahan New Contributor
New Contributor

Re: Help with dax

Ah, I set this up like a measure, but creating this as a column makes your original code make more sense.

Instead of using CALCULATE() (which forces DAX to return a single value from a table context), you can use an IF statement instead to only apply this if the data in the row meets your conditions:

 

IF ( 'GetIssues'[status.name] <> "Closed" && 'GetIssues'[fixVersions.name] = BLANK() || 'GetIssues'[fixVersions.name] <> "Test",
    SWITCH( TRUE(),
        TODAY() - 'GetIssues'[created] <= 5, "0-5 days",
        TODAY() - 'GetIssues'[created] <= 15, "6-15 days",
        TODAY() - 'GetIssues'[created] <= 30, "16-30 days",
        TODAY() - 'GetIssues'[created] <= 60, "31-60 days",
        TODAY() - 'GetIssues'[created] <= 90, "61-90 days",
        "90+ days" 
    ),
BLANK()
)

Again, I still think the condition at the top is written wrong.  I think it will apply this to every row where the FixVersion isn't Test, but that's an easier problem to fix.

8 REPLIES 8
Super User
Super User

Re: Help with dax

Would be easier if you gave us some sample data and told us what the intended behaviour is, huge nested ifs aren't nice to read

diskovered Member
Member

Re: Help with dax

My apologies, it's for ticket management where I need to find ticket aging: 0-5 days, 6-15, 16-30, etc. I just need help cleaning the dax for its intended purpose. 

Cmcmahan New Contributor
New Contributor

Re: Help with dax

What exactly is the issue you're having?  Is this running slow so you want query performance help? Is it giving the wrong result?

 

I'm guessing by looking at this that you're having an issue where tickets are appearing in the wrong buckets.  If there's a ticket that's 14 days old, I'm guessing it shows up as 16-30 days instead of the 6-15 days.  If this is the case, removing the +5, +15, etc from your date calculations should fix that.  

 

It looks like there might also be an issue where all records with a GetIssues[fixVersions.name] of "Test" come back as 0-5 days.

 

I would rewrite this entire thing as a SWITCH statement, using CALCULATE(SWITCH(...),FILTER(...)) style syntax to apply the filters that are the same between all of the cases.

Cmcmahan New Contributor
New Contributor

Re: Help with dax

Here's a quick SWITCH setup that you can adapt as needed.  I'm still unsure about the filter options, the OR seems out of place.  Are you trying to apply this to Issues where the status is not Closed and the fix version is blank or not equal to Test? That will just return all issues where the name isn't Test.

 

CALCULATE(
    SWITCH( TRUE(),
TODAY() - 'GetIssues'[created] <= 5, "0-5 days",
TODAY() - 'GetIssues'[created] <= 15, "6-15 days",
TODAY() - 'GetIssues'[created] <= 30, "16-30 days",
TODAY() - 'GetIssues'[created] <= 60, "31-60 days",
TODAY() - 'GetIssues'[created] <= 90, "61-90 days",
"90+ days"
),
FILTER(ALL(GetIssues), GetIssues[status.name] <> "Closed" && (GetIssues[fixVersions.name] = BLANK() || GetIssues[fixVersions.name] <> "Test") )
)

 

Also, are you setting this up as a calculated column, or as a measure?

diskovered Member
Member

Re: Help with dax

Thank you for your help. I think we're almost getting there.  

I am using calculated column at the moment. Problem now is it can't find 'GetIssues'[created]' column. Does SWITCH only support measure?

 

switch.PNG

diskovered Member
Member

Re: Help with dax

Thank you for your help. I think we're almost getting there.  

I am using calculated column at the moment. Problem now is it can't find 'GetIssues'[created]' column. Does SWITCH only support measure?

 

switch.PNG

Cmcmahan New Contributor
New Contributor

Re: Help with dax

Ah, I set this up like a measure, but creating this as a column makes your original code make more sense.

Instead of using CALCULATE() (which forces DAX to return a single value from a table context), you can use an IF statement instead to only apply this if the data in the row meets your conditions:

 

IF ( 'GetIssues'[status.name] <> "Closed" && 'GetIssues'[fixVersions.name] = BLANK() || 'GetIssues'[fixVersions.name] <> "Test",
    SWITCH( TRUE(),
        TODAY() - 'GetIssues'[created] <= 5, "0-5 days",
        TODAY() - 'GetIssues'[created] <= 15, "6-15 days",
        TODAY() - 'GetIssues'[created] <= 30, "16-30 days",
        TODAY() - 'GetIssues'[created] <= 60, "31-60 days",
        TODAY() - 'GetIssues'[created] <= 90, "61-90 days",
        "90+ days" 
    ),
BLANK()
)

Again, I still think the condition at the top is written wrong.  I think it will apply this to every row where the FixVersion isn't Test, but that's an easier problem to fix.

diskovered Member
Member

Re: Help with dax

I think this will work, I will work on the conditions. Thank you for your help.