Forum Discussion
How to avoid aggregation?
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
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.
1 Reply
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.