Below is a picture of my current model which works fine for one company. I now need to create a P&L which stretches across four companies, combined. The problem here is that I could have one account number, say 455000, but that could mean “Rent” for company one and “Electricity” for company two. In other words, I can no longer simply rely on a “Accnumber” relationship. I need to now rely on a “Company Accnumber” relationship. Is it really as simple as creating a new “merged” field in my two tables called “CompanyAccnumber” and then defining my relationships off of this? My company names are quite long, to give you an idea: XXXXX XXXXX XXXXX (PTY) LTD.
Is this the best practice way, or is there something better I need to consider?
Yes creating a new Key is how you could deal with this but instead of the really long company names you can define any custom Key.
I have multiple companies in my datasets also, here’s how I deal with that:
First load the company list into a separate query, added an Index column as Company Key
in all subsequent queries, retrieve the Company Key before expanding the tables in PQ
Create a DIM Key by combining the Company Key with the Dimension Key(s) in your case “Accnumber”, the “Accnumber Key” will now be a unique value in each Company.
I guess the real challenge will be how to visualize that, if you want to aggregate values across companies you’ll have to ‘translate’ all Accnumbers so the values will end up in the right place…
Hi Melissa, as always thank you for your comprehensive reply. I’ll have to do five different queries as the companies are from 5 different databases. And then… I think I’ll have to append them all into one table and follow your instructions. Thank you, I’ll attempt to do so. Step 1 is quote clear, I may get back with a couple of questions on steps 2 and 3 if you don’t mind.
Side Comment / your thoughts
I was discussing this with someone yesterday more seasoned than I. He said the best solution would be to write a script to append the database tables and create a new “All_Companies” database. I will eventually figure out how to do that, but at the moment I’ll depend on Power Query. His comment was that in spite of how PowerBI (PowerQuery) is advertised, it is not really meant to do a whole lot of heavy transformational lifting. Now this is news to me. I cannot see how this can be the case. Surely once you have refreshed the model and all the SQL has run, it doesn’t even matter. It is a subjective question, but do you have any thoughts?
In my view Retrieving Data or Data Transformations should be pushed back as far as possible to the source. So I agree the order should be: SQL => PQ => DAX
Having a well designed and purposeful model for your Report will make the DAX easier and faster.
