Adding a related field or vlookup to CALCULATETABLE / SUMMARIZE

[jch]

Link to google drive with example here: https://drive.google.com/drive/folders/1lmydbCnGZ19mP37_8EI80B_YBnIi99VG?usp=sharing

DS1 and DS2 are imported (see screenshot below) and DS3 is a Dax table using the code snippet below. Does anyone have suggestions for adding Salesperson from DS1 to DS3? 

ds3 = CALCULATETABLE(

SUMMARIZE('ds2', 'ds2'[Amount], ds2[transaction id]

) , 'ds2'[Amount] > 90.00

)

[bcdobbs · Accepted solution]

Hi,
ds1 is on the one side of a one to many relationship with ds2. Therefore all of it's columns are present in the expanded table form of ds2. (See Expanded tables in DAX - SQLBI ).

You can therefore just do the following:

ds3 = 
CALCULATETABLE(
    SUMMARIZE(
        'ds2', 
        'ds1'[Salesperson],
        'ds2'[Amount], 
        ds2[transaction id]),
    'ds2'[Amount] > 90.00
)