Create either/or (conditional) relationships in Power BI

[IMRGZ]

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.

[IMRGZ]

@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

ID1	ID2
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

ID3	ID4	feature
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

ID3	ID4	feature
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 · Accepted solution]

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

@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

[Greg_Deckler]

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