Issues with Model when fetching data with

[PBIUser-Sap]

Hi All,

I have an issue as, Unable to fetch data into report with multiple tables.

RLS needs to applied on Master but when we try to bring data from Categories and Transactions in one report table it shows empty. So please suggest if the current model remains the same and UserMap can filter Category too
I also changed the Direction between Transactions and Categories to both and turned on apply security filter on both but still no luck

PBIX file here,

https://drive.google.com/file/d/1-q-VXKlp0n_ACb7S4N2aeEMSZYabyMj6/view?usp=sharing

[HotChilli]

This is not a row level security problem.  You can prove this by removing all RLS and see if the data is shown.  Have you debugged it in the way I suggested?

--

I can only give general advice about the model (I have no idea what your requirements are).  It looks to me like the model is based on a relational database and has not been altered for dimensional modelling.  So my general advice is find out what data the users want to see in visualisations, find out how they want to slice and dice that data. 

[HotChilli]

There's a data problem and it's causing the links between Master-Transactions to break.  To debug this, build some simple table visualisations to test the relationships between each pair of tables in the chain e.g. Master-Contract

, Contract-JobOrder 

. If you put one field from each table in the visualisation, you'll see which one is blank and that will be the problem.

-----

For the longer term I don't really like the model.  It's not a star schema, it's got many to many relationships and dual direction relationships so have a think about remodelling as well.

Good luck.

[PBIUser-Sap]

Hi @HotChilli ,

I updated the query on this topic as,

RLS needs to applied on Master but when we try to bring data from Categories and Transactions in one report table it shows empty. So please suggest if the current model remains the same and UserMap can filter Category too
I also changed the Direction between Transactions and Categories to both and turned on apply security filter on both but still no luck.

We have many to many here, For example between UserMap and Master we jave join between LE & Entity cloumns. And in our case one company/vendor can have multiple LE assigned and one LE can be assigned to multiple vendors/companies. How do you suggest to work around on it?

[PBIUser-Sap]

And also for bidirectional relationship,

I had a meeting with MS for one of the issues where the consultant said one of the best practices is to keep direction in both sides so that both can filter each other , and since we don't have to create any reports and just expose the model so the report user might need filter in any direction which is why after consulting with our functional consultant came to the decision of keeping cross filter direction as both. Please advice