Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hello all,
I have a rather large model used for building a P&L report for one of my clients (among other things). I want to combine 3 existing tables into one using DAX, and add columns for 6 measures for each unique combination of Company ID, Account ID and Period. The columns I'm interested in from the existing tables look like this:
Company ID |
1 |
2 |
3 |
4 |
etc. etc. (26 companies) |
Accounts ID |
F0001_3000 |
F0001_3001 |
F0001_3002 |
F0001_3010 |
etc. etc. (based on a standardized accounts plan; similar for each company but accounts name may differ) |
Period (yyyymm) |
201801 |
201802 |
201803 |
201804 |
etc. etc. (right now the date table goes until December 2020) |
Expected/wanted result:
Company ID | Accounts ID | Period | Measure 1 | Measure 2 | Measure 3 | Measure 4 | Measure 5 | Measure 6 |
1 | F0001_3000 | 201801 | aaa | eee | iii | mmm | qqq | uuu |
1 | F0001_3001 | 201801 | bbb | fff | jjj | nnn | rrr | vvv |
1 | F0001_3002 | 201801 | ccc | ggg | kkk | ooo | sss | www |
1 | F0001_3010 | 201801 | ddd | hhh | lll | ppp | ttt | xxx |
... | ... | ... | ... | ... | ... | ... | ... | ... |
I've tried joining the 3 tables using CROSSJOIN (VALUES() ) as they are not related to each other in the model, and this almost gives me what I want. The catch, however, is that my client is a group consisting of 26 companies, but I do not have 26 individual 'Accounts' tables. Rather I've combined all the relevant Accounts tables from the ERP system into one large table consisting of more than 7000 rows. This is not taken into account when the engine gets the list of unique values from the 'Accounts'[Accounts ID] column, meaning it combines all 7000 Account IDs with all 26 Company IDs. I don't want all that, though, and so my question is this:
Is there a way to combine the ID of each company with only the Account IDs relevant to that specific company, given that I only have access to one Accounts table for all 26 companies? And how can I make sure I can still add a column for each of the 6 measures I mentioned? (the last part is less important right now)
All help is greatly appreciated 🙂
Solved! Go to Solution.
@Anonymous - OK, make sure there is a relationship between company table and accounts table and then you should be able to do this:
Table =
GENERATE(
GENERATE('Companies', SELECTCOLUMNS(RELATEDTABLE(Accounts),"Accounts ID",[Accounts ID])),
'Periods'
)
The inner GENERATE will create the cartesian product of companies and their related accounts. Then, the outer generate will use this to then create a cartesian product of the result of the inner GENERATE and the periods table.
@Anonymous - I believe you could do it in DAX like this:
Table = GENERATE(GENERATE(DISTINCT('Table1'[Column]),DISTINCT('Table2'[Column]),DISTINCT('Table3'[Column]))
@Greg_Deckler - thanks for the suggestion but using that code I still face the same problems as I described in my original post.
DISTINCT( 'Accounts'[Account ID] ) returns a list of approx. 7,000 accounts but only a relatively small subset of those accounts are relevant to each company in the group. See below screenshot (in Norwegian) for more details, here the column for Company ID is filtered to only include company #1. You can see that the column for Accounts ID includes values related to company #123, which is what I don't want (the "F0" in front of 123 is just a prefix from the ERP system):
"Selskap" means company, "Konto" means account, and "periode" is period
@Anonymous - OK, so is there a column in the Accounts table that has what Company it belongs to?
@Greg_Deckler - yes, there is. There is no column for period in the Accounts table though.
@Anonymous - OK, make sure there is a relationship between company table and accounts table and then you should be able to do this:
Table =
GENERATE(
GENERATE('Companies', SELECTCOLUMNS(RELATEDTABLE(Accounts),"Accounts ID",[Accounts ID])),
'Periods'
)
The inner GENERATE will create the cartesian product of companies and their related accounts. Then, the outer generate will use this to then create a cartesian product of the result of the inner GENERATE and the periods table.
@Greg_Deckler - thank you, that worked (with a few minor modifications to your code to get only the columns I'm interested in from each table)! 😃
Here is my final code if anyone is interested (translated into English for your convenience):
@Anonymous - Awesome, glad you got it!
Hi , @Anonymous
Can you share more sample data of your "Accounts table "?
Best Regards,
Community Support Team _ Eason
@Anonymous , I did not get but you can put a filter on top cross join to match some data.
This can also help you https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Hi @Anonymous ,
Not sure why you are trying to achieve this using DAX. You can easily do this in Query Editor by merging these 3 tables on your common columns.
Thanks,
Pragati
Hello @Pragati11,
I'm aware of that, but as I stated in my original post the tables are not related to each other. What I mean by that is that there are no obvious common columns. The tables are all dimensions connected to a 'General Ledger' fact table as well as a 'Budget' fact table.
My objective in doing this is to enhance the performance of a matrix visualisation in my report. I've tried implementing variables in my DAX code without any noticeable effect and now I'm trying to change my model to see if that'll help.
Thanks anyway!
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.