Date Range Based on User

[WishAskedSooner]

Hi All,

I have a data model with numerous users' data. Each user's data has a custom date range. For example, User A's data ranges from January 2020 to March 2024 and User B's data ranges from January 2022 to June 2024. Furthermore, the data model is filtered by user at an API level rather than RLS, so User A can't view User B's data and vice versa. Also, I have a single Fact table with all the User data with a Dim Date table.

One problem I am encountering, however, is my tables and date slicers range across the full date range i.e. January 2020 to June 2024. Therefore, when User A is logged in, the tables with their data extends to June 2024 with blanks for the months past March 2024, and User B sees a bunch of blank rows from January 2020 until their data begins in January 2022.

I have read perhaps a custom RLS table is the solution although I don't really know what that is. I have also contemplated making a Dim Date table that has a User column, but that would seem to defeat the purpose of a dimension table. Therefore, I am wondering what the best practice solution would be to this problem.

Any help is appreciated!

[GilbertQ]

Hi @WishAskedSooner 

What you can do is? put in a philtre on your dateslaser where the number. is greater than zero, which will then falter this laser to only show the roads where the user has data. Here is an example

Filtering calculation items in a slicer - SQLBI

[WishAskedSooner · Accepted solution]

I found a solution that seems preferable over creating a Dim Dates table that has a User column as this would greatly multiply the size of my Dim Date table (there are many more Users than the two cited above).

I set the cross-filtering direction between the DimDate table and the Fact table to Both instead of Single. I also set the cross-filtering direction to Both between the Dim User table and the Fact table. Now, it works as expected.

I am marking this as the solution, but feel free to comment if there are potential pitfalls to this solution that I am unaware of.

[GilbertQ]

Hi @WishAskedSooner 

Just a word of caution is when the cross filter is set to both, any other queries or filters done where it connects to the Fact table will then also go and filter the date table. So there could be instances where there are the wrong values!