Hi
Dataset: (this is shorten down to the important columns)
| User table | Comment |
| Email adr | RLS is filtering on this column based on logged in user email |
| Contact ID | a unique key |
User table have a "1 -> many" relationship with connection table
| Connection table | Comment |
| Contact ID | This is connected to user table |
| Organization number ID | One user can have access to multiple organizations. 1 row per organization per user. |
Connection table have a "many -> 1" relationship with organization number table which is turned into bidirectional with check applied security filter
| Organization number table | Comment |
| Organization number ID | This is connected to connection table, a unique key |
| Organization number | 1 organization have 1 ID |
Organization number table have 2 "1 -> many" relationship with Vendor and customer table. They both go the same direction.
My dataset have 2 rows which each containing 1 facts table. This is where they splitt.
| Customer table | comment | Vendor table | comment |
| Customer ID | a unique key | Vendor ID | a unique key |
| Organization number | This is connected to Organization number table. No customers are sharing an organization number | Organization number | This is connected to Organization number table. No vendors are sharing an organization number |
My customer table is connected to my Purchase order headers (fact table) with a 1 -> many relationship. 1 customer can have many Purchase orders.
My vendor table is connected to my Sales order headers (fact table) with a 1 -> many relationship. 1 vendor can have many Sales orders.
| Purchase order headers | comment | Sales order headers | comment |
| Purchase order ID | a unique key | Sales order ID | a unique key |
| Customer ID | connected to customer table | Vendor ID | connected to vendor table |
| Ship ID | One order is in one shipment, but a shipment can contain many orders. | Ship ID | One order is in one shipment, but a shipment can contain many orders. |
| Date | | Date | |
| Status | | Status | |
Each of my header tables have their own table that describes the order in detail. This is a 1 -> many relationship from header table to line table. The line table is describing down to each product in the order.
| Purchase order lines | comment | Sales order lines | comment |
| Purchase order ID | This is connected to sales order headers table. 1 order can have many lines. | Sales order ID | This is connected to Purchase order headers table. 1 order can have many lines. |
| Produkt ID | There exsits 1 row per product per order. | Produkt ID | There exsits 1 row per product per order. |
| Sales order ID | | Purchase order ID | |
Each of my line table is in the end connected to a product table with a many -> 1 relationship.
| Product table | comment |
| Produkt ID | a unique key |
| Product name | |
This report is mostly about giving the user an overview between the sales and purchases he does and comparing them, but the report is build up so that the purchases is following the sales. For each sales order there is a corresponding purchases order and they both have the same Ship ID.
In my reports there is a few slicers on Ship-ID, date and status. These slicers comes from 2 reference table which both is build from my sales order headers table.
| SO Ship reference table | Comment | Connected to | Relationship |
| Ship ID | This is a unique ID, this is where my ship id slicer comes from | Sales order headers | 1 -> many |
| | | Purchase order headers | 1 -> many |
Date and status is seperated in a second table
| SO status/date reference table | Comment | Connected to | Relationship |
| Ship ID | | SO Ship reference table | Many -> 1 with biderectional security filter |
| Status | This is where my date slicer comes from | | |
| Date | This is where my status slicer comes from | | |
| Vendor ID | This could be here if needed, but i cant connect this to Vendor table as it would be a 1 -> many relationship from vendor to this reference tabel. This would cause multiple ways to purchase order headers/lines from my organization table which is my RLS entry point. | | |
I can't have status and date in my SO Ship reference table because then this table won't have a unique ship IDs anymore. As Ship ID is not unique in my Sales/Purchases order headers tables. There is a many to many relationship between these 3 fields.
So, finally to my problem. My RSL does not filter my slicers. i have tried a few different solutions but non is working. My most promising was creating a measure in my Ship ID reference table that would tell if the ship ID exsist in Purchase order headers after RLS has filtered out the rows that shouldnt be there, but never got it to work.
Someone have an idea? i haven't found any similar problems so far on the internet.
