Conditional Filter On Different Tables

[hnguyen76]

Hi All.

I have an optimization question. Currently I have a measure that returns the correct values in which I'm looking for. The measure is as followed:

VAR _DateFilter = DATESBETWEEN(Dim_Calendar[Date], _StartDate, _EndDate)

VAR _RegExp = 
SWITCH(_SelectedValue,
"AB", CALCULATE([Base_IBT], _DateFilter, ALL(Dim_PL_Hierarchy)), 
"CB", CALCULATE([Base_IBT], _DateFilter, ALL(Dim_Geo_Hierarchy)),
"ZY", CALCULATE([Base_IBT], _DateFilter)
)


RETURN

DIVIDE(_RegExp, _Threshold)

As you can see, I'm calling my measure three times with each filter criteria being slightly different. My measure could be evaluated three times even if it is nested within the SWITCH statement. More information on optimizing calculations could be found here:
https://www.sqlbi.com/articles/optimizing-mutually-exclusive-calculations/

So, what I want to know is... is there a way for me to conditionally set which tables I select instead of calculating three times? Something similar to what I want to do:

VAR _DateFilter = DATESBETWEEN(Dim_Calendar[Date], _StartDate, _EndDate)

VAR _FilterCriteria = // Of course, this does not work
SWITCH( _SelectedValue,
"AB", ALL(Dim_PL_Hierarchy),
"CB", ALL(Dim_Geo_Hierarchy),
"ZY", BLANK()
)

// I want to build something like this, instead
VAR _RegExp = CALCULATE([Base_IBT], _DateFilter, _FilterCriteria)

RETURN

DIVIDE(_RegExp, _Threshold)

Hopefully this is somehow possible. Any help would be greatly appreciated!!

[ImkeF]

Hi @hnguyen76 ,
sorry, I didn't pay enough attention to your actual use case.
While the {} is valid when directly used as a table replacement in a CALCULATE for example,

it cannot be used in a SWITCH statement. Just like any other table expression cannot be used in there:
SWITCH and IF have to return scalar values, so you cannot create conditional table expressions in DAX variables with them unfortunately.

[lbendlin]

Been beating around this for a while.  I think this error message says it most succinctly

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

I haven't found a way around this.  Looks like your original approach is the only one that works since each calculate filter there really only uses one column. It does not seem to be possible to hot swap columns inside a filter.

The {} or ALL() thing is a red herring, unrelated to the issue.

[lbendlin]

Show the formula for [Base_IBT]

[hnguyen76]

Hey Ibendlin, here you go as requested. It's just a typical base measure:

[lbendlin]

or by ALL()  ?

[ImkeF]

Hi @hnguyen76 ,
your code will probably work if you replace the Blank()  (which returns a scalar value) by an empty table {} :

VAR _FilterCriteria = // Of course, this does not work
SWITCH( _SelectedValue,
"AB", ALL(Dim_PL_Hierarchy),
"CB", ALL(Dim_Geo_Hierarchy),
"ZY", {}
)

[lbendlin]

"

Of course, this does not work

"

Care to elaborate?  You can assign tables to variables.  Filters are tables.  ("Everything in DAX is a table").  So this should work just fine, if a bit wasteful (especially for large tables)

[hnguyen76]

@ImkeF , unfortunately Power BI is throwing an error using your suggestion:

@lbendlin , tried your method too. Seems to throw an error as well:

Any ideas on why it's saying that the expression refers to multiple columns?