topic Re: One store with two possible IDs, how do I relate the fact table and dimension table? in DAX Commands and Tips

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

Royal_Trudel — Wed, 11 Jan 2023 17:51:59 GMT

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?

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

FreemanZ — Thu, 12 Jan 2023 00:32:15 GMT

hi Royal_Trudel

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

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

v-zhangti — Thu, 12 Jan 2023 03:19:50 GMT

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.