Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional filter within calculate function?

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?
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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 )

6 Replies

  • goncalogeraldes's avatar
    goncalogeraldes
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        AlexisOlson
        Super User
        4 years ago

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