Dynamic join of three different column which are not blank

[userdata]

Hi,

I have two 3 different ids and I need to do a dynamic join based on the column that are not null. 

This is one table where different notes are stored and looks like this;

Table1

id1  id2  id3  notes

3                     deded

        2

                7

4       5     8

fact

id1 id2 id3 sales

3     3     2   5

So the fact table has all ids and there is no blanks in there. In the table1 there can be nulls and i want to join table1 on fact based on what is not null in table1 : like  if id2 is the only one that has a value , then join only id2 on fact, if id1 has only a value then join id1 on the fact etc. How is this possible?

[v-xiaoyan-msft]

Hi @userdata ,

I'm not quite sure if I understand your needs accurately.
Sample data without sensitive information and expected output would help tremendously.
Please see this post regarding: 

How-to-Get-Your-Question-Answered-Quickly 

How-to-provide-sample-data-in-the-Power-BI-Forum 

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

Where is Sales coming from?

Are each IDs different dimensions or are they the same dimension listing different values?

[userdata]

@PaulDBrown  these are different dimensions but all coming from the same table

[PaulDBrown]

OK. Can you elaborate on what you are expecting to achieve? I don't quite understand what the expected output should be. Perhaps recreate an example of the output in Excel and post the depiction?

[userdata]

I just uploaded the file here https://github.com/userdata21/test/blob/main/Dynamic_Join.pbix

So in fact we have all ids and entities. What we want to bring to the table visual is to see the notes but based on a dynamic join. The user can in Table 1 either only put iD and a notes , or a name and a comment, or can put an ID, ID2,Name and notes alltogehter based upon that the notes are only to be seen at ID level or ID2 level etc. Im not sure how to change the merge or join based on the different conditions as in the fact all ID, ID2 and name will be will be always there but in tavble 1 there can be blanks but not in fact

[PaulDBrown · Accepted solution]

I would recommend you set up the model with dimension tables for common fields in both tables. You can the use the fields from the dimension tables in visuals, measures, slicers, filters etc....

I've attached the sample PBIX file