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, ...
Need Help
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

    hmm, doesn't this approach require multiple values in a column?
    say I have X,Y in following format
    X|Y
    A1|A1
    A1|B1
    so in this case the Result should be something like this (to properly filter B1):
    Result
    A1
    A1,B1
    I think in this case it's best to create separate table for the slicer with just A1:H1, and propagate filter to X & Y from there