Re: Adding a Calculated Table to Model

yasbos · ‎01-12-2023

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.

yasbos · ‎01-12-2023

@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.

FreemanZ · ‎01-12-2023

hi @yasbos

i see . indeed considerable sunk cost. you may also try combine columns from both sides and relate on the combined coulmn.

In the long term, for cases like this, i would insist to suggest appending tables in PQ.

yasbos · ‎01-13-2023

Hi, @FreemanZ . Thanks for your help. I just thought I'd share with you that my suggested alternative last night (group by date) made no sense to me in the morning LOL, as it would present the same problem. So, what I am actually planning to do is to summarize everything that I need on that same page in the same table. This way I can use the week range filter from this summary table -- instead of the one from the calendar table. Thanks so much for your help.

grantsamborn · ‎01-13-2023

Have you tried making a copy of your date table to use when creating your summarized table?

It might come down to how you created your date table. I'm not sure if this will work with a date table created in DAX but Power Query (or from source) should work.

yasbos · ‎01-13-2023

Thanks @grantsamborn . I did think about doing that this morning, but then, as you mentioned, it would probably not work if it is a DAX copy (will probably lead to circular reference again), and will further complicate my already crowded model. If I do it in power query, that will most likely work, but, again, there will be yet another table in the model. I will see if the self-sufficient dax table that I'm now creating will get the job done. If I hit a roadblock, I will then resort to creating another calendar (slimmed down) in power query. Thanks so much.

yasbos · ‎01-12-2023

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.