Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
@Matti , Try a new table like
summarize(filter(Table, not(isblank(Table[ParrentKey]))), Table[AccTree],Table[ParrentKey])
I managed to solve my problem. Have a look at my solution below.
Turns out GenerateAll was the function I was looking for.
GENERATEALL (
@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.
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!
I managed to solve my problem. Have a look at my solution below.
Turns out GenerateAll was the function I was looking for.
GENERATEALL (
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |