topic Re: Conditional filter within calculate function? in DAX Commands and Tips

Conditional filter within calculate function?

Anonymous — Fri, 04 Mar 2022 17:42:58 GMT

I am trying to create a measure that has a conditional filter within the Calculate function.

Here is a stripped down version of what I am trying to do.

PersonaPortfolio = a table that is filtered down based on the USERPRINCIPALNAME. It's just a two column table, User and Portfolio.

FilterPortfolio = I am filtering the fact table where values in the Portfolio field exist in the PersonaPortfolio table.

This works as long as there is the 'Persona Choices (Portfolio)' table is not empty.

My problem is that I want to ignore any filters if the 'Persona Choices (Portfolio)' is empty, but I get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value" whenever I try to put an if statement into the filter portion of my calculate function.

Is there any way to do handle this without putting that if statement in the filter portion of the calculate function?

Re: Conditional filter within calculate function?

goncalogeraldes — Fri, 04 Mar 2022 18:03:04 GMT

Hello there Anonymous ! Does this work for you?

Test = VAR PersonaPortfolio = CALCULATETABLE ( VALUES ( 'Persona Choices (Portfolio)'[Portfolio] ), 'Persona Choices (Portfolio)'[User] = USERPRINCIPALNAME () ) RETURN SWITCH ( TRUE (), ISEMPTY ( PersonaPortfolio ), CALCULATE ( [Delta-P], FILTER ( fact_DeltaP, NOT ( fact_DeltaP[Portfolio] IN PersonaPortfolio ) ) ), CALCULATE ( [Delta-P], FILTER ( fact_DeltaP, fact_DeltaP[Portfolio] IN PersonaPortfolio ) ) )

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Re: Conditional filter within calculate function?

Anonymous — Fri, 04 Mar 2022 20:05:40 GMT

goncalogeraldes This does get rid of that error. Thank you!

The next level of complexity would be having 2 conditions within that calculate function. Is that a possibility using the same switch function logic?

Re: Conditional filter within calculate function?

AlexisOlson — Fri, 04 Mar 2022 21:04:25 GMT

The IF function can't output tables, only single values, but you could rewrite this more like

SWITCH ( TRUE (), ISEMPTY ( A ) && ISEMPTY ( B ), CALCULATE ( [Delta-P], IgnoreA, IgnoreB ), ISEMPTY ( A ) && NOT ISEMPTY ( B ), CALCULATE ( [Delta-P], IgnoreA, FilterB ), NOT ISEMPTY ( A ) && ISEMPTY ( B ), CALCULATE ( [Delta-P], FilterA, IgnoreB ), NOT ISEMPTY ( A ) && NOT ISEMPTY ( B ), CALCULATE ( [Delta-P], FilterA, FilterB ) )

Re: Conditional filter within calculate function?

Anonymous — Fri, 04 Mar 2022 22:23:02 GMT

AlexisOlson This worked, thank you!

I have 4 different tables it is referencing, A,B,C, and D. Your example is showing A and B. Due to this, I needed to have 16 of those scenarios listed out in my code. I'm afraid of it getting out of control if I need to add any more tables. Are you familiar with a more concise way to handle this or is the code you provided the only solution?

Re: Conditional filter within calculate function?

AlexisOlson — Fri, 04 Mar 2022 22:50:43 GMT

Yeah, I think you can refactor it to combine FilterA and IgnoreA like this:

Test = VAR A = CALCULATE ( [...] ) VAR B = CALCULATE ( [...] ) VAR C = CALCULATE ( [...] ) VAR D = CALCULATE ( [...] ) VAR FilterA = FILTER ( fact, IF ( ISEMPTY ( A ), NOT fact[A] IN A, fact[A] IN A ) ) VAR FilterB = FILTER ( fact, IF ( ISEMPTY ( B ), NOT fact[B] IN B, fact[B] IN B ) ) VAR FilterC = FILTER ( fact, IF ( ISEMPTY ( C ), NOT fact[C] IN C, fact[C] IN C ) ) VAR FilterD = FILTER ( fact, IF ( ISEMPTY ( D ), NOT fact[D] IN D, fact[D] IN D ) ) RETURN CALCULATE ( [Delta-P], FilterA, FilterB, FilterC, FilterD )

Re: Conditional filter within calculate function?

Anonymous — Mon, 07 Mar 2022 15:32:57 GMT

AlexisOlson This is exactly what I was looking for! Thank you so much!