Retrieve columns from a Direct Query table into a Import Mode table, performing an aggregation too.

[D_PBI]

Hi
I have the following Import Mode table named 'Sales':

I also have the following Direct Query table named 'Branch':

I would like to perform a SUM on the Amount column, per TYPE and return those calculated values to the Sales table. The end result should look like the following table:

Ther are no relationships joined/connected between the tables. However, the joining fields would be Sales[Ref No.] to Branch[Code].
How do I complete this in DAX?
Thanks.

[D_PBI]

@Anonymous - thank you.

[Anonymous · Accepted solution]

Hi @D_PBI ,

If you don't want to create a relationship, you can try creating a calcualted table using DAX.

Table 2 = FILTER(CROSSJOIN('Table','Table_1'),[Ref No.]=[Code])

It's the result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[thamzaoui]

ok 

how to do that with in power query by merging and group by 

[D_PBI]

BUMP.

[D_PBI]

Thanks for your efforts but you cannot do that with a composite model.

[thamzaoui]

Hi

No need for DAX. You can do this easily with Matrix visualization

[D_PBI]

No, you can't. You need a method to join the two tables via DAX. There is no relationship set up in the model pane. I need to understand how to achieve my request in the OP.