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"
)
)
Wrap what you want to define as filters in the DEFINE, then reference them in each EVALUATE.
DEFINE
VAR CommonFilters =
FILTER(
CQD,
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)"})
)
VAR EndTimeFilter1 =
FILTER(
CommonFilters,
CQD[End Time] >= dt"2024-07-17T00:00:00" &&
CQD[End Time] < dt"2024-07-17T06:00:00"
)
VAR EndTimeFilter2 =
FILTER(
CommonFilters,
CQD[End Time] >= dt"2024-07-17T06:00:00" &&
CQD[End Time] < dt"2024-07-17T12:00:00"
)
VAR EndTimeFilter3 =
FILTER(
CommonFilters,
CQD[End Time] >= dt"2024-07-17T12: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]
),
EndTimeFilter1
)
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]
),
EndTimeFilter2
)
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]
),
EndTimeFilter3
)