Forum Discussion

misul's avatar
misul
Helper I
8 years ago

Nested IF formulas with OR statement

Hello PBI Community, 

What would be a way to simplify this nested if statement?

 

Existing columns, X, Y related to other dimension tables.

 

Result=
IF (related (X)=A1 || related (Y)= A1, A1,

IF (related (X)=B1 || related (Y)=B1, B1,

IF (related (X)=C1 || related (Y)=C1, C1,

IF (related (X)=D1 || related (Y)=D1, D1,

IF (related (X)=E1 || related (Y)=E1, E1,

IF (related (X)=F1 || related (Y)=F1, F1,

IF (related (X)=G1 || related (Y)=G1, G1,

IF (related (X)=H1 || related (Y)=H1, H1,

"None" ))))))))

 

 Thank you for your support!

 

 

 

Need Help

5 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    Hi misul

    I agree with Stachu's logic, and you could implement this something like below.

    Note that this depends on the lookup values being the same as the return values.

     

    Result =
VAR ValueList = { "A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1" }
VAR X =
    RELATED ( YourTable[X] )
VAR Y =
    RELATED ( YourTable[Y] )
VAR LookupResult =
    FIRSTNONBLANK ( ValueList, IF ( OR ( [Value] = X, [Value] = Y ), 1 ) )
RETURN
    IF ( ISBLANK ( LookupResult ), "None", LookupResult )

    Regards,

    Owen

    • misul's avatar
      misul
      Helper I
      8 years ago

      Stachu OwenAuger Thanks for your tips. For my original problem your solution helps.

       

      However, I have run into another issue which is that the last item from ValueList ("H1") is never returned,because of its position at the end of the list.

       

      The new problem is that :

       

       

       

      I am trying to create 1 new column [Result] based on a two columns [X] and [Y]. Both columns [X] and [Y] have the same 8 distinct values (A1... H1).

       

      If I filter for A1, I want it to return all rows where both [X] is A1 and [Y] is A1.

       

      • OwenAuger's avatar
        OwenAuger
        Super User
        8 years ago

        misul

        Sure - should be able to do that.

        Just to clarify, do you want a way of filtering on the values A1..H1, then if, for example someone filters on A1, you return the union of rows where either [X] is A1 or [Y] is A1?

         

        Could you post a quick example to confirm?

         

        Thanks

        Owen

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    can you share the sample data from tables containing X,Y, and the table where you add the calculated column?

    my way of thinking would be - if you could determine A1-H1 with VAR then the syntax could be like this

    Result = 
VAR varParameter = "A1:H1" //some proper syntax here
RETURN
IF(RELATED(X)=varParameter || RELATED(Y) = varParameter, varParameter, "None")