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]))
    )
IMRGZ's avatar
IMRGZ
Icon for Helper I rankHelper I
5 years ago

Greg_Deckler Thank you for pointing this out.

I have created a sample pbi file and I will post the sample data as well.

I have created a wetransfer link for  the pbi file:

https://we.tl/t-yM4YVjrqhO

The data in the file is the following:

Fact table

ID1   Usage
0000-0001  2
0000-0002  5
0000-0002  4
0000-0003  7
null  8
0000-0004  10
0000-0005  5
null  2
0000-0015  6
0000-0020  4

 

Lookuptable1

ID1ID2
0000-0006  0000-0001
0000-0007  0000-0002
0000-0008  0000-0004
0000-0009  0000-0005
0000-0013  0000-0012
0000-0015  0000-0016
0000-0018  0000-0019
0000-0020  0000-0020
0000-0021  0000-0004

 

Lookuptable2

 

ID3ID4feature
null  null  A
0000-0001  0000-0001  B
0000-0002  0000-0010  C
0000-0011  0000-0004  D
0000-0005  0000-0009  E
0000-0013  0000-0013  F
null  null  G
0000-0016  0000-0017  H

 

And the result I am trying to get by first matching ID1 with ID2 with ID3 and then the ramainder with ID4.

The remainder that isn't match I want to match via the route ID1 with ID4 and the reminder with ID3.

 

Result

ID3ID4feature
null  null  A
0000-0001  0000-0001  B
0000-0002  0000-0010  C
0000-0011  0000-0004  D
0000-0005  0000-0009  E
0000-0013  0000-0013  F
null  null  G
0000-0016  0000-0017  H

 

  • 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