Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hnguyen76
Resolver II
Resolver II

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

 

 

8 REPLIES 8
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

lbendlin
Super User
Super User

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
Super User
Super User

Show the formula for [Base_IBT]

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

base_ibt.png

lbendlin
Super User
Super User

or by ALL()  ?

ImkeF
Super User
Super User

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", {}
)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

lbendlin
Super User
Super User

"

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)

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

 

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

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.