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 first match the ID from my fact table (ID1) with ID2 from lookup table 1 based on ID1. Then I want to try and match this ID2 first with ID3 from lookup table 2 to get the dimension data I need from this table. All values that do not get a match should then try and match ID2 from lookup table 1 with ID4 from lookup table 2 to get the dimension data. Then I want all values that are still not matched to be matched based on ID1 from my fact table (which is the same as ID1 from lookup table1) with ID3 and finally I want whatever values that remain to try and match with ID4 from lookup table 2. This way I would be able to match about 84% of all my fact table ID's and get the dimension data from Table2 for these rows.

 

I hope I made it clear like this and that perhaps someone can help.

 

Is is possible to make these kinds of conditional relationships in power bi?

 

Thank you for your help.

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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    IMRGZ The sort answer is probably. You can use LOOKUPVALUE or MAXX(FILTER(...)...) to look things up in different tables but the text-based explanation gets confusing. Really need sample data.

     

    Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • 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