Conditional filter within calculate function?

[Anonymous]

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?

[goncalogeraldes]

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

[Anonymous]

@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?

[AlexisOlson]

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

[Anonymous]

@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?

[AlexisOlson · Accepted solution]

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 )

[Anonymous]

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