How to avoid aggregation?

[cokeyng]

Hi all,

I am new to Power BI. One problem I am trying to solve is to refer to a column in a different table in my formula. But the inner join seems to multiple the number of rows and sum them up, when I am using the sumx function. 

In SAS, I was able to manage that by using the AggregateTable and Table functions, like this:

AggregateTable(_Sum_,

Table(_Max_, Fixed('Program'n, 'Special Program'n, 'Quartile'n, 'Fiscal Year'n, 'Site'n, 'Address'n),

'X_Actual Resident Days'n))

Is there a best practice to handle this situation in Power BI?

Thanks

Cokey

[rubayatyasmin · Accepted solution]

@cokeyng 

you can use RELATED() / RELATEDTABLE() to reference column from another table. make sure tables are connected in the model view. 

example: suppose you have a table named TableA and you want to sum up the 'X_Actual Resident Days' from another table named TableB, you can create a measure as:

Total_X_Actual_Resident_Days =
SUMX(
RELATEDTABLE(TableB),
TableB[X_Actual Resident Days]
)

refer:

 https://learn.microsoft.com/en-us/dax/related-function-dax

https://learn.microsoft.com/en-us/dax/relatedtable-function-dax

And for duplicating rows, do you have many-to-many relationships? Maybe that is causing the duplicates.