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

        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?