One store with two possible IDs, how do I relate the fact table and dimension table?

[Royal_Trudel]

Hello fellow users! I've run into a small issue when dealing with my fact and dimension tables.

In our franchise, we have two iFood stores for some of our operations (not all of them) for marketing reasons... The problem is that, for instance, I can't relate ID 85 to Store2, for instance.

Below is an example of how my fact table looks:

And here is an example of my dimension table:

As you can see, some of the stores don't have a second ID.

Any ideas on how I could relate these informations?

[v-zhangti · Accepted solution]

Hi, @Royal_Trudel 

You can try the following methods.
New Table:

Table = 
Var _Table1=SUMMARIZE('DIMENSION','DIMENSION'[ID1],'DIMENSION'[Store])
Var _Table2=SUMMARIZE('DIMENSION','DIMENSION'[ID2],'DIMENSION'[Store])
Return
FILTER(UNION(_Table1,_Table2),[ID1]<>BLANK())

Result:

Is this the result you expect? If not, let us know what you expect the output to be.

Best Regards,

Community Support Team _Charlotte

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

[FreemanZ]

hi @Royal_Trudel 

your sample data seems unable to reflect the issue. are you going to update it? Or could you explain further?