Attached is management accounts where I am showing actual income and expenditure against either budget or reforecast. As it stands, there is no issue for me to review the information on the Desktop. I have shared the report online and have set some RLS where people assigned to a specific Department will be able to see only the information that relates to their department. Until here, all is fine.
The problem arises when users try to access the report. The information is not displaying and the error basically says that because, both, Actual and forecast data are linked to the Department reference table, it can’t show the information.
When I try to delink the forecast from the department, the error fixes itself but then my totals play weird and they don’t tally.
I would like to know how I can tweak my data model so that I can achieve the same thing and get rid of the double link to the Department.
Just a guess here, but I wonder if it has something to do the fact the Department table is a calculated table using the Actuals table. Any chance you could create that in Power Query and not a calculated column and see if that helps?
Thank you, @Nick_M. The calculated table is produced using distinct DAX which creates a dynamic but unique list. How would I create a dynamic list in query editor?
Thank you, @Nick_M and @Melissa. I have used the method and been able to create the list. However, when I try to create a relationship, it gives me the cardinality error and doesn’t allow me to have a 1 - * (1= Department and * = Actuals/ Forecast).
