I created a dax measure that iterates thru an if/else condition based on the selected values of two filters as follows:
[New Measure] =
VAR _DateType = SELECTEDVALUE(DimDateType[Date Type])
VAR _GrossNet = SELECTEDVALUE(DimGrossNet[Gross Or Net])
VAR _GrossService = CALCULATE(SUM('FactGLExpenses'[Gross Expense]) ,USERELATIONSHIP('DimDate'[DateID],'FactGLExpenses'[ServiceDateID]))
VAR _GrossAccounting = SUM('FactGLExpenses'[Gross Expense])
VAR _NetService = CALCULATE(SUM('FactGLExpenses'[Net Expense]) ,USERELATIONSHIP('DimDate'[DateID],'FactGLExpenses'[ServiceDateID]))
VAR _NetAccounting = SUM('FactGLExpenses'[Net Expense])
RETURN
IF(_GrossNet="Gross" && _DateType="Service Date",_GrossService,
IF(_GrossNet="Gross" && _DateType="Accounting Date",_GrossAccounting,
IF(_GrossNet="Net" && _DateType="Service Date",_NetService
,_NetAccounting) ) )
When running the below query against my new dax measure matching the criteria of the final else condition, it correctly send 1 query to my DB.
DEFINE
VAR __DS0FilterTable = TREATAS({"Accounting Date"}, 'DimDateType'[Date Type])
VAR __DS0FilterTable2 = TREATAS({"Net"}, 'DimGrossNet'[Gross Or Net])
VAR __DS0FilterTable3 = TREATAS( {"Test Cost Center"}, 'DimCostCenter'[Cost Center Name] )
VAR __DS0FilterTable4 = TREATAS({"2022"}, 'DimDate'[Year])
VAR __DS0Core = SUMMARIZECOLUMNS(
'DimCostCenter'[Cost Center Name],
'DimInvoice'[Invoice Number],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
"New_Measure", 'FactGLExpenses'[New Measure] )
VAR __DS0BodyLimited = TOPN(30002, __DS0Core, 'DimCostCenter'[Cost Center Name], 1, 'DimInvoice'[Invoice Number], 1)
EVALUATE
__DS0BodyLimited
ORDER BY 'DimCostCenter'[Cost Center Name], 'DimInvoice'[Invoice Number]
Here is a trace showing 1 correct query running when calling the dax measure with the criteria matching the final else condition:
However if the criteras in my query is anything else, 3 queries are sent to my DB one of which is querying for all Invoice Numbers in the invoice table which has over 1,000,000 records causing the below error in Power BI:
Any thought on why the three queries are generated instead of one single query?
Here is a trace showing 3 queries running when calling the dax measure
