Forum Discussion

IMRGZ's avatar
IMRGZ
Icon for Helper I rankHelper I
5 years ago
Solved

Create either/or (conditional) relationships in Power BI

Hi all, I have some difficulties explaining what I want to do, since it is not something I work with on a regular basis, but I made a visual representation of what I'm trying to do: I want to...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    IMRGZ I cannot fathom why the result table is the same as LookupTable2. If  you have LookupTable2 and LookupTable2 is what you want, then why not just use LookupTable2? In any case, still not understanding what you are going for, I did this:

    Table = 
    FILTER(
        ADDCOLUMNS(
            'Fact table',
            "feature",
            IF(
                NOT(ISBLANK(MAXX(FILTER('Lookup Table2','Lookup Table2'[ID3]=[ID1]),[feature]))),
                MAXX(FILTER('Lookup Table2','Lookup Table2'[ID3]=[ID1]),[feature]),
                MAXX(FILTER('Lookup Table2','Lookup Table2'[ID4]=[ID1]),[feature])
            )
        ),
        NOT(ISBLANK([feature]))
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

IMRGZ I cannot fathom why the result table is the same as LookupTable2. If  you have LookupTable2 and LookupTable2 is what you want, then why not just use LookupTable2? In any case, still not understanding what you are going for, I did this:

Table = 
    FILTER(
        ADDCOLUMNS(
            'Fact table',
            "feature",
            IF(
                NOT(ISBLANK(MAXX(FILTER('Lookup Table2','Lookup Table2'[ID3]=[ID1]),[feature]))),
                MAXX(FILTER('Lookup Table2','Lookup Table2'[ID3]=[ID1]),[feature]),
                MAXX(FILTER('Lookup Table2','Lookup Table2'[ID4]=[ID1]),[feature])
            )
        ),
        NOT(ISBLANK([feature]))
    )
IMRGZ's avatar
IMRGZ
Icon for Helper I rankHelper I
5 years ago

Greg_Deckler I adjusted it a bit and am really close to what I would like to achieve. The only thing still missing in the table below is the "feature" value for ID1 "0000-0015" which should be "H" based on translating ID1["0000-0015"] into ID2["0000-0016"] which then matches with ID3["0000-0016"] from lookup table2 and thus results in feature "H".

The formula I used:

Table = 
        ADDCOLUMNS(
            'Fact table',
            "feature",
            IF(
                'Fact table'[ID1]="null","",
                IF(
                NOT(ISBLANK(MAXX(FILTER('Lookup Table2','Lookup Table2'[ID3]=[ID1]),[feature]))),
                MAXX(FILTER('Lookup Table2','Lookup Table2'[ID3]=[ID1]),[feature]),
                MAXX(FILTER('Lookup Table2','Lookup Table2'[ID4]=[ID1]),[feature])
            )
        ))

Thank you for all your help. I am going to read into this, because I was trying to solve this in a completely different manner.

I have updated the example file:

https://we.tl/t-BP3JgA7Q5o

 

Thank you for all your help so far