same table lookupvalue multiple values where single value was expected

[JuliaDRX]

Hi together,

i really looking for a good tip for my following problem , it really drives me crazy !! 

It is all in the same table - no realtionship needed. 

Both columns have multiple values - unique values .

Line 1 is clear, if WWL_FACT_FACT_ID_HOST is empty - please use FACT_ID_Host

IF (mdm_v2_factorylist[WWL_FACT_FACT_ID_HOST]="",mdm_v2_factorylist[FACT_ID_HOST], HERE LOOKuPVALUE for LINE 2+3 ??)

Line 2 and 3 is my problem : if WWL_FACT_FACT_ID_HOST is not EMPTY and/or  1N=1N same - please look in WWL_FACT_FACT_ID_HOST where FACT_ID_HOST is different and use that one >> 1M

Thanks a lot for your help !! 

Line	FACT_ID_HOST	WWL_FACT_FACT_ID_HOST	that should be the solution
1	1M	"empty"	1M
2	1M	1N	1M
3	1N	1N	1M	if this is same please look for not same and use this

[SpartaBI · Accepted solution]

@JuliaDRX 

Result = 
IF(
    Tabelle1[WWL Werk] <> BLANK(),
    Tabelle1[Werk Nr. HOST],
    VAR _search = Tabelle1[Werk Nr. HOST]
    RETURN
    CONCATENATEX(
        FILTER( 
            Tabelle1,
            Tabelle1[WWL Werk] = _search
        ),
    Tabelle1[Werk Nr. HOST] 
    )
)

      

Showcase Report – Contoso By SpartaBI

[JuliaDRX]

LookupValue does not work - because of multiple values where single expected.

[SpartaBI]

@JuliaDRX 
do you want to do a quick zoom to explain and I'll send back a solution file?