Help with dax

[diskovered]

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

[Cmcmahan]

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]

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?

[diskovered]

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?

[Cmcmahan · Accepted solution]

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]

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

[Cmcmahan]

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.

[jthomson]

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]

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.