I admit, I am just starting out with DAX Statements. What I found out was that the MSFT Call Quality Data Warehouse will CLIP DAX request at 10K rows, . So I am trying to use the DAX Studio “Multiple EVAULATE” feature to get 500K or so records to a CSV file.
So I need to write EVALUATES, each with their own Date filter. (a bit of a pain, since you can’t have multiple DEFINES).
My issue is that I am struggling as to how to add a FILTER to the final EVAULATE statement. Is there some other way to do this filtering for each EVALUATE (I plan to generate the EVAL statements in PYTHON) since there will be about 50 of them. (DAX Studio does NOT support different VAR DEFINES, for each EVALUATE.
ALSO important to understand is that the OLAP warehouse that supports Teams CALL Quality does not really understand DAX expressions. So you can’t do anything other than SUM (No AVG, MEAN, or other aggregators). See link above.
This code returns values, but it is not filtered.
EVALUATE
SUMMARIZECOLUMNS(
CQD[Date],
CQD[Day Of Week],
CQD[Total Call Count]
)
ORDER BY
CQD[Date] ASC,
CQD[Day Of Week] ASC,
CQD[Total Call Count] ASC
This pseudo-code for one call I would like to do:
But it retunes and empty dataset:
EVALUATE
FILTER(
SUMMARIZECOLUMNS(
CQD[Second Network Name],
CQD[Total Call Count],
CQD[Date],
CQD[End Time],
CQD[Day Of Week]
),
AND(
CQD[End Time] >= DATE(2023, 7, 1),
CQD[End Time] < DATE(2024, 7, 22)
)
)
ORDER BY
CQD[Second Network Name] ASC,
CQD[Total Call Count] ASC,
CQD[Date] ASC,
CQD[Day Of Week] ASC
This code is generated by PowerBI, and it filters, but it is not suitable for DAX studio, and multiple EVALAUTES.
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"First-to-Second"}, 'CQD'[Stream Direction])
VAR __DS0FilterTable2 =
TREATAS(
{"MediaConnectivityError",
"Other",
"PickupTimedOut"},
'CQD'[CDR Response Reason]
)
VAR __DS0FilterTable3 =
TREATAS({"Server"}, 'CQD'[First UserType])
VAR __DS0FilterTable4 =
TREATAS({"Inside"}, 'CQD'[Second Inside Corp])
VAR __DS0FilterTable5 =
TREATAS({"Wired"}, 'CQD'[Second Network Connection Detail])
VAR __DS0FilterTable6 =
TREATAS({"User",
"Anonymous"}, 'CQD'[Second UserType])
VAR __DS0FilterTable7 =
FILTER(
KEEPFILTERS(VALUES('CQD'[Session Type])),
AND('CQD'[Session Type] IN {"Conf"}, SEARCH("Conf", 'CQD'[Session Type], 1, 0) = 1)
)
VAR __DS0FilterTable8 =
TREATAS({" : Client",
"Client : Client",
"Client : Server"}, 'CQD'[Is Server Pair])
VAR __DS0FilterTable9 =
FILTER(
KEEPFILTERS(VALUES('CQD'[Packet Utilization])),
NOT('CQD'[Packet Utilization] IN {"(Blank)",
"062: [0 - 0]",
"064: [1 - 2)"})
)
VAR __DS0FilterTable10 =
TREATAS({"NonTest"}, 'CQD'[Test Call Type])
VAR __DS0FilterTable11 =
FILTER(
KEEPFILTERS(VALUES('CQD'[End Time])),
AND('CQD'[End Time] >= DATE(2024, 6, 10), 'CQD'[End Time] < DATE(2024, 7, 22))
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('CQD'[Second Network Name], "IsGrandTotalRowTotal"),
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__DS0FilterTable7,
__DS0FilterTable8,
__DS0FilterTable9,
__DS0FilterTable10,
__DS0FilterTable11,
"SumSecond_User_Count", CALCULATE(SUM('CQD'[Second User Count])),
"SumAvg_Packet_Loss_Rate_Max", CALCULATE(SUM('CQD'[Avg Packet Loss Rate Max])),
"SumTotal_Stream_Count", CALCULATE(SUM('CQD'[Total Stream Count])),
"SumP95_Latency", CALCULATE(SUM('CQD'[P95 Latency])),
"SumTotal_Call_Dropped_Failure_Percentage", CALCULATE(SUM('CQD'[Total Call Dropped Failure Percentage])),
"SumTotal_Call_Dropped_Stream_Count", CALCULATE(SUM('CQD'[Total Call Dropped Stream Count])),
"SumAvg_Round_Trip_Max", CALCULATE(SUM('CQD'[Avg Round Trip Max])),
"SumAvg_Call_Duration", CALCULATE(SUM('CQD'[Avg Call Duration])),
"SumTotal_Call_Count", CALCULATE(SUM('CQD'[Total Call Count]))
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
[SumSecond_User_Count],
0,
'CQD'[Second Network Name],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
[SumSecond_User_Count] DESC,
'CQD'[Second Network Name]