Simplifying DAX Statement

[DrYSG]

In order to get data from the MSFT Teams Call Quality Data warehouse (OLAP). https://learn.microsoft.com/en-us/microsoftteams/dimensions-and-measures-available-in-call-quality-d...

There are a bunch of restrictions:

• The PBI connector only works in desktop mode, not in the cloud
• It makes a DirectQuery Connection
• It cannot handle anything other than the simplest DAX (only SUM, MAX) no MEAN, AVG, etc.
• It chops the result at 10K rows (so Ineed to find a way to do multiple EVALAUTES)

I have something working in DAX studio, but DAX allows only one DEFINE for Multiple EVALAUTES, is there a way to factor out some of the common terms with VAR or MEASURE so that I can trim the EVALAUTE just to deal with CQD[End Time]

```

EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS(
            CQD[Second Network Name],
            CQD[Second Subnet],
            CQD[Date],
            CQD[Day Of Week],
            CQD[Hour],
            CQD[Total Call Count],
            CQD[Avg Network Jitter Max],
            CQD[Audio Poor Percentage],
            CQD[Video Poor Percentage],
            CQD[Avg Round Trip Max]
        ),
        CQD[Stream Direction] = "First-to-Second",
        CQD[CDR Response Reason] = "OK",
        CQD[First UserType] = "Server",
        CQD[Second Inside Corp] = "Inside",
        CQD[Second Network Connection Detail] = "Wired",
        CQD[Test Call Type] = "NonTest",
        CQD[Session Type] = "Conf",
        CQD[Is Server Pair] IN {
            "Client : Server",
            " : Client"
        },
        CQD[Second UserType] IN {
            "User",
            "Anonymous"
        },
        NOT (CQD[Packet Utilization] IN {
            "(Blank)",
            "062: [0 - 0]",
            "064: [1 - 2)"
        }),
        AND(
            CQD[End Time] >= dt"2024-07-17T00:00:00",
            CQD[End Time] < dt"2024-07-17T18:00:00"    
        )
    )
```

[Gaga_Jin]

@DrYSG  

Try this one, i think its more readable than yours.

VAR FilterDirection = CQD[Stream Direction] = "First-to-Second"
VAR FilterReason = CQD[CDR Response Reason] = "OK"
VAR FilterUserType = CQD[First UserType] = "Server" && CQD[Second UserType] IN {"User", "Anonymous"}
VAR FilterNetwork = CQD[Second Inside Corp] = "Inside" && CQD[Second Network Connection Detail] = "Wired"
VAR FilterCallType = CQD[Test Call Type] = "NonTest" && CQD[Session Type] = "Conf"
VAR FilterServerPair = CQD[Is Server Pair] IN {"Client : Server", " : Client"}
VAR FilterPacketUtilization = NOT (CQD[Packet Utilization] IN {"(Blank)", "062: [0 - 0]", "064: [1 - 2)"})
VAR FilterEndTime = AND(CQD[End Time] >= dt"2024-07-17T00:00:00", CQD[End Time] < dt"2024-07-17T18:00:00")

EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS(
CQD[Second Network Name],
CQD[Second Subnet],
CQD[Date],
CQD[Day Of Week],
CQD[Hour],
CQD[Total Call Count],
CQD[Avg Network Jitter Max],
CQD[Audio Poor Percentage],
CQD[Video Poor Percentage],
CQD[Avg Round Trip Max]
),
FilterDirection,
FilterReason,
FilterUserType,
FilterNetwork,
FilterCallType,
FilterServerPair,
FilterPacketUtilization,
FilterEndTime
)

[Anonymous]

Hi @DrYSG 

Maybe you can try this:

FILTER (
    SUMMARIZE (
        CQD[Second Network Name],
        CQD[Second Subnet],
        CQD[Date],
        CQD[Day Of Week],
        CQD[Hour],
        CQD[Total Call Count],
        CQD[Avg Network Jitter Max],
        CQD[Audio Poor Percentage],
        CQD[Video Poor Percentage],
        CQD[Avg Round Trip Max]
    ),
    CQD[Stream Direction] = "First-to-Second"
        && CQD[CDR Response Reason] = "OK"
        && CQD[First UserType] = "Server"
        && CQD[Second Inside Corp] = "Inside"
        && CQD[Second Network Connection Detail] = "Wired"
        && CQD[Test Call Type] = "NonTest"
        && CQD[Session Type] = "Conf"
        && CQD[Is Server Pair]
            IN { "Client : Server", " : Client" }
                && CQD[Second UserType]
                    IN { "User", "Anonymous" }
                        && NOT ( CQD[Packet Utilization] IN { "(Blank)", "062: [0 - 0]", "064: [1 - 2)" } )
                            && AND (
                                CQD[End Time] >= dt"2024-07-17T00:00:00",
                                CQD[End Time] < dt"2024-07-17T18:00:00"
                            )
)

Given that you have too many filters to omit, this formula doesn't simplify much.

Best Regards

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