I am creating a solution where multiple customer databases are sourced into Power BI. RLS makes sure that when any of the customers log in, only their own data is visible to them.
However, I have a standard Calendar table related to the fact tables of the model, and I need the Fiscal Month and Fiscal Year columns to be dynamic, depending on which customer logs on. To complicate things, each customer can consist of several companies, which in some occasions may have varying Fiscal Calendars. In this case the Customer will be able to select a company in a slicer, and the Fiscal columns of the Calendar tables should shift according to this. Is this even possible, as calculated columns are only updated at load?
How should I go about this?
Yes and no. I found a workaround relying on a table with fiscal periods per customer in my source, and the case changed slightly, so that Fiscal periods varies only on a customer level, making it somewhat more simple.
I can accept your solution though, but I did not have time to test it?
Thanks for the input 🙂, I thought about something like this, but the solution will dynamically add new customers, and potentially the model will contain upwards of 100s of them...
Well this is a complicated solution and it is very labour intensive and a horror to maintain but I can't of any other way to achieve what you need. This is actually drawns from a blogpost I am drafting right now on how to get the current security group of the user in DAX.
Create a dimtable with all security groups in it and add rules for every group to this table.
Now, you can create different datetables with inactive relations to your facttable and use SWITCH() command to determine what relationship needs to be activated based on if [Groups] contains GroupA for example (it wouldn't if the current user was in GroupB),
Again, this is very cumbersome and I wouldn't recommend it but it was the only way I could think of right now 🙂
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.