Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

New table based on combination of column values from existing tables

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 IDAccounts IDPeriodMeasure 1Measure 2Measure 3Measure 4Measure 5Measure 6
1F0001_3000201801aaaeeeiiimmmqqquuu
1F0001_3001201801bbbfffjjjnnnrrrvvv
1F0001_3002201801cccgggkkkooossswww
1F0001_3010201801dddhhhlllppptttxxx
...........................

 

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 🙂 

1 ACCEPTED 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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

11 REPLIES 11
Greg_Deckler
Community Champion
Community Champion

@Anonymous - I believe you could do it in DAX like this:

 

Table = GENERATE(GENERATE(DISTINCT('Table1'[Column]),DISTINCT('Table2'[Column]),DISTINCT('Table3'[Column]))



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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"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?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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):

 

Test_table v3 =
GENERATE(
GENERATE(
DISTINCT( 'Companies'[Company ID] ),
SELECTCOLUMNS(
RELATEDTABLE( 'Accounts' ),
"Account ID", 'Accounts'[Account ID]
)
),
DISTINCT( 'Date'[Period] )
)

@Anonymous - Awesome, glad you got it!



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Hi , @Anonymous 

Can you share more sample data of your  "Accounts table "?

 

Best Regards,
Community Support Team _ Eason

amitchandak
Super User
Super User

@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/

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

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!    

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.