Merge / Expand two calculated tables into one

[Matti]

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.

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 

AccID	AccTree	ParrentKey
1	1
2	1	1
3	1	1
4	1	2
5	1	2
1	2
2	2	1
3	2	1
4	2	2
9	2	3

[amitchandak · Accepted solution]

@Matti , Try a new table like

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

[amitchandak · Accepted solution]

@Matti , Try a new table like

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

[Matti]

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

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

Example data provided below.

AccID	AccTree	ParrentAccID
1	1	1
2	1
3	1
4	1	4
5	1
6	1
7	1
8	2	8
9	2
10	2	10
11	2
12	3	12
13	3
14	3
15	3

Thank you!

[Matti · Accepted solution]

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])))))