Forum Discussion

EnderWiggin's avatar
EnderWiggin
Helper I
3 years ago
Solved

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 table...
  • Greg_Deckler's avatar
    3 years ago

    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 = 
            ADDCOLUMNS(
                __BaseTable,
                "Username", MAXX(FILTER('User Table', [Id] = [UserId]),[Name]),
                "Rank", RANKX(__BaseTable, [Sum Quantity],,DESC)
            )
        VAR __Result =
            SELECTCOLUMNS(
                FILTER(__Table, [Rank] <= 3),
                "Username", [Username],
                "Sum Quantity", [Sum Quantity]
            )
    RETURN
        __Result