Forum Discussion

hnguyen76's avatar
hnguyen76
Resolver II
5 years ago

Conditional Filter On Different Tables

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

 

 

Need Help

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    "

    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's avatar
      hnguyen76
      Resolver II
      5 years ago

      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?

  • ImkeF's avatar
    ImkeF
    Community Champion
    5 years ago

    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", {}
)
    • hnguyen76's avatar
      hnguyen76
      Resolver II
      5 years ago

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

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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.

  • ImkeF's avatar
    ImkeF
    Community Champion
    5 years ago

    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.