Two sets of Relations against single reference table

Hi everyone,

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.

Thank you.

Management_Accounts.pbix (1.6 MB)

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?

In Power Query you’d use something like this to extract a single column with distinct values from another query.

let
    DistinctList = List.Distinct( QueryName[ColumnName] )
in
    DistinctList

Here’s a link to the online M Language documentaton.
https://docs.microsoft.com/en-us/powerquery-m/list-distinct

Can also use the GUI:

1 Like

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

Here is the file for your reference:
Management_Accounts.pbix (1.6 MB)

Ignore me. Found the issue. I’ll report back.

Hi @Nick_M and @Melissa. Just wanted to let you know that the solution suggested has worked and people are able to see the information. Thank you.