I have the following two tables:
I would like to add a calculated column to the Author table showing the total number of pages in all the books the author has written.
In SQL I would solve the problem by writing a view like this (or using that code in a trigger to populate the calculated column):
SELECT
"Author"."Author ID"
(SELECT sum("Page count") FROM Book WHERE "Author ID" = "Author"."Author ID") AS "Total pages"
FROM "Author";
How to achieve something like that in Power BI?
Since you want to add the column to the dimension table (one side of the one-to-many relationship) you'll have to use RELATEDTABLE() instead of the RELATED() function:
Page Count = SUMX(
RELATEDTABLE(Book),
Book[Page Count]
)
The row context in your calculated column gets transfered to a filter context in the fact table (Books). This feature is know as context transition.
Of cause your SQL would rather return a table like this one:
Author Pages =
SELECTCOLUMNS(
Author,
"Author ID", Author[Author ID],
"Page Count", SUMX(
RELATEDTABLE(Book),
Book[Page Count]
)
)
However, if you are just interested in visualizing the numbers you don't need any of the above expressions, but just have to drag Author ID and Page Count into a Table visual.
