topic Re: Simplifying DAX Statement in DAX Commands and Tips

Simplifying DAX Statement

DrYSG — Mon, 22 Jul 2024 14:57:29 GMT

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-dashboard

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

Re: Simplifying DAX Statement

Anonymous — Wed, 24 Jul 2024 02:14:46 GMT

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.

Re: Simplifying DAX Statement

Gaga_Jin — Fri, 26 Jul 2024 09:03:18 GMT

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