Forum Discussion
Adding a Calculated Table to Model
Hi. I am trying to summarize metrics from six tables by week range. All six of them have the same category of metrics but for different business units. Each row in each of the tables has a date column. I have all six of them related to a calendar table by the date columns. The calendar table has a column for the week (i.e. week: Nov. 13 to 19; Nov. 20 to 26; etc. ) and that's the week range that I need to summarize by.
I summarized my metrics using summarize columns for each of the six tables and used union to combine them. Now, I need to visualize them in a summary table in a Power BI visual. That all seems to work well. The problem is that there are other visuals using the same six tables and all are subject to the value of a slicer that is using the week range from the Calendar table. I'm using the week range in the summary table because I need to summarize the values by week. right now, the slicer won't affect the visual that's based on the summarized calculated table because it is not related to the calendar table in the model. And now, here is the problem: if I try to relate the calculated table to the calendar table using the week range, I get a circular reference error.
So, in short, the column that I'm using to summarize (group) the date by is the week range. However, it is the same column that I need to relate to the calendar table. When I do that, I get a circular reference error. I need to relate it to the calendar table because I need the visual to be affected by the week range (from the calendar table) slicer on the page.
Thanks so much.
7 Replies
hi yasbos
try to append six tables in POWER QUERY first and related the calendar table with the appended table only.
FreemanZ thanks so much. I will try. You see, I have calculated columns in each table and are needed in the summary and are lready being used in the existing measures and visulas. I suppose I can calculate these columns in power query, but I'm afraid that this exercise will baloon out of control. I was hoping if there was a way to somehow dodge that vexing circular reference.
Thanks, FreemanZ . Please, elaborate on the suggested solution. I didn't quite get it. I need to link back to the calendar table using the week range that is in the calculated table (summarizecolumns) which itself (week range) comes from the calendar table -- oh my, just saying that made me not blame power bi for not allowing it. It's a clear circular reference. Oh, well. Ok, I will try tomorrow morning to use the date column of the calendar table in the summarizecolumns instead of using the week range column. I hope that then when I use the week range column in the visual, the metrics will be summarized at the week range, and, at the same time, it will allow me to use the week range in the model relationship.
