Modeling Relationship

klee7290 · ‎04-29-2024

I have a unique issue that I am not sure can be solved.

I have two data sets with the following fields:

-Table A - Spend family, Category, Sub-category, country, global lead, regional lead

-Table B - Spend family, Category, Sub-category, country, spend amount, PO number + more

I am trying to bring in the global and regional lead from Table A to have the proper drill down of spend per lead from Table B. However, there are cases where there are multiple leads for the same category, sub-category, country. For example, I tried concatnating country, category, sub-category to create a unique ID (duplicates exist in both tables bc there are sometimes multiple leads for the same country.category.sub-category ID). I also have tried using a bridge table leveraging country but it shows me a whole instead of a piece (e.g., if the spend for Spend Family in France was $15, it would show $15 for every category that is associated with the Spend Family).

There does not seem to be a way to get a unique ID out of table A. Anyone have any suggestions?

v-huijiey-msft · ‎04-29-2024

Hi @klee7290 ,

Thanks for the reply from NaveenGandhi .

Please consider adding an additional field, lead, to the connection string to make it truly unique.

For example: country category sub-category lead.

This can be done in Power Query.

If you have any other questions please feel free to contact me.

I would be grateful if you could provide me with pbix file, please remember not to log into your account in Power BI Desktop when uploading the pbix file.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

v-huijiey-msft · ‎04-29-2024