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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Matti
Frequent Visitor

Merge / Expand two calculated tables into one

Hi,

 

Update: Initial question is solved, but didn't fully reflect the case. Additional info provided further down.

 

I am trying to build a calculated table that merge/expand two calculated tables in the same DAX code. But there might be another way to do it - query editor (M script) is not an option here.

 

Here's what I am trying to do illustrated in Excel. I need to create a calculated table with the unique combinations of AccTree and ParrentKey.

 

Distinct Calculated table.PNG

 

 

 

 

 

 

 

 

 

 

So basically I get can my distinct AccTrees and the distinct ParrentKeys that go with the trees in two separate calculated tables. But I just can't figure out how to merge the two into one table - without having to actually calculate two separate tables and then add them up in a third calculated table.

 

Distinct AccTree = SELECTCOLUMNS(SUMMARIZE(Acc,Acc[AccTree]),"AccTree",Acc[AccTree])

Distinct ParrentKey = CALCULATETABLE(DISTINCT(Acc[ParrentKey]),FILTER(ALL(Acc),Acc[AccTree]= [AccTree]))

 

I hope someone out there is up for the challenge.

 

Thank you,

 

Example data 

AccIDAccTreeParrentKey
11 
211
311
412
512
12 
221
321
422
923

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Matti , Try a new table like

summarize(filter(Table, not(isblank(Table[ParrentKey]))), Table[AccTree],Table[ParrentKey])

View solution in original post

Matti
Frequent Visitor

I managed to solve my problem. Have a look at my solution below.

 

Turns out GenerateAll was the function I was looking for.

 

GENERATEALL (

  CALCULATETABLE (
    SELECTCOLUMNS (
      Acc),
      "AccID, Acc[AccID],
      "AccTree", Acc[AccTree])),
  CALCULATETABLE(
    DISTINCT(Acc[ParrentAccKey]),
      FILTER(
        ALL(Acc),
        Acc[AccTree]=[AccTree] && NOT(ISBLANK(Acc[ParrentAccKey])))))

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Matti , Try a new table like

summarize(filter(Table, not(isblank(Table[ParrentKey]))), Table[AccTree],Table[ParrentKey])

@amitchandak you solved my initial question, thanks! 

 

However, I did not word it quite right or provide a proper example. So let me try again.

 

Distinct Calculated table.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

So for each AccID, I need to add a row for each unique ParrentActKey within that account tree.

 

Example data provided below.

AccIDAccTreeParrentAccID
111
21 
31 
414
51 
61 
71 
828
92 
10210
112 
12312
133 
143 
153 

 

Thank you!

Matti
Frequent Visitor

I managed to solve my problem. Have a look at my solution below.

 

Turns out GenerateAll was the function I was looking for.

 

GENERATEALL (

  CALCULATETABLE (
    SELECTCOLUMNS (
      Acc),
      "AccID, Acc[AccID],
      "AccTree", Acc[AccTree])),
  CALCULATETABLE(
    DISTINCT(Acc[ParrentAccKey]),
      FILTER(
        ALL(Acc),
        Acc[AccTree]=[AccTree] && NOT(ISBLANK(Acc[ParrentAccKey])))))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.