Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calendar table with dynamic FY columns

Hi,

 

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?

Need Help

5 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    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.

    Table dim_SecurityGroups

    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 🙂

     

    Kind regards

    Djerro123

    -------------------------------

    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! 🙂

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago
    Hi Anonymous ,

    Is this problem sloved?
    If not, please let me know.
     
    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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?