Nested IF formulas with OR statement

[misul]

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!

[OwenAuger]

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]

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

[Stachu]

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

[OwenAuger]

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

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