cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Create calculated table from two tables

Hi All,

I would like to create a measure which shows top3 users by name based on their summarized activity values from 'User table' and 'User Activity table'. The filter connection between the tables is 'User table' 1 - many 'User activity table'

The measure result should be a calcualted tabe with two columns: User table [Name], and sum of [Quantity] from User Activity table.

I tried to do it with CALCULATEDTABLE, TOPN and ADDCOLUMN functions, but I can not figure out the correct measure definition. Could you help me to solve this? Thank you very much!

1 ACCEPTED SOLUTION
Super User

@EnderWiggin Try below. PBIX is attached below signature.

``````Username Sum Quantity Top 3 =
VAR __BaseTable =
SUMMARIZE(
'User Activity Table',
[UserId],
"Sum Quantity", SUM('User Activity Table'[Quantity])
)
VAR __Table =
__BaseTable,
"Username", MAXX(FILTER('User Table', [Id] = [UserId]),[Name]),
"Rank", RANKX(__BaseTable, [Sum Quantity],,DESC)
)
VAR __Result =
SELECTCOLUMNS(
FILTER(__Table, [Rank] <= 3),
"Sum Quantity", [Sum Quantity]
)
RETURN
__Result``````

@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Helper I

@Greg_Deckler thank you very much for your detailed and clear solution, this is what I wanted.

Super User

@EnderWiggin Try below. PBIX is attached below signature.

``````Username Sum Quantity Top 3 =
VAR __BaseTable =
SUMMARIZE(
'User Activity Table',
[UserId],
"Sum Quantity", SUM('User Activity Table'[Quantity])
)
VAR __Table =
__BaseTable,
"Username", MAXX(FILTER('User Table', [Id] = [UserId]),[Name]),
"Rank", RANKX(__BaseTable, [Sum Quantity],,DESC)
)
VAR __Result =
SELECTCOLUMNS(
FILTER(__Table, [Rank] <= 3),
"Sum Quantity", [Sum Quantity]
)
RETURN
__Result``````

@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...