Data Mismatch

[moe_elbohsaly]

My colleagues are noticing that there is a data mismatch between my dashboard and theirs. Although both of us refreshed simultaneously, there is still some difference. Can anyone help us out?

[moe_elbohsaly]

Honestly speaking, I didn't quite get your point. May you rephrase?

[Anonymous]

Really sorry about that. RLS stands for Row-Level-Security. This is used in the following scenario.

1. You have a large dataset.
2. You have developed a report or dashboard based on this dataset.
3. This report will be viewed by many users.
4. But all of them are not authorized to see all the data. Each user is authorized to view the report which is based on a subset of the data which is filtered based on either a country or a product category or whatever the user is typically accountable/responsible for.

Sometimes, inadvertently, a customer or product or something else appears in the fact table/transaction table, but the corresponding master data won't be there in the master data table. This causes DAX to create a blank row in the master data table. See the example below...

Table: Customers

Table: Sales

Relationships

Create a measure: 

Customer Count = COUNTROWS(ALL(Customers))

Result of this measure:

Although the customer master table has only two records, because of the customer "C" which is missing in the customer master table, DAX added a blank row to the customer table to satisfy the relationship and mapped the blank row of the customer master to the sales table's record of customer "C". Therefore COUNTROWS( ALL(Customers) ) returned 3 instead of the actual count of 2 records in that table.

All these theories will look irrelevant to your issue. But if you have the Row-Level-Security enabled, the transactions of the customer "C" in the sales table won't appear for other users' views. But it will be appearing in your elevated authorization level and as a result, it might look like a data mismatch in terms of the figures.

See the screenshots below...

It is the same report, viewed as a different role. The critical thing to note is that the measure that counts the rows of ALL(Customers) is giving two different results because of RLS.

When the RLS is enforced, it will give the intended result and it won't cause any data mismatch. But usually, the RLS is configured on master data tables which are in turn connected to transaction tables. But if the master data tables have some missing records, and the transaction tables have transaction records of those missing records, it will result in a scenario that resembles a data mismatch between users and that is very difficult to identify by the users. Someone with an elevated authorization might be able to spot the difference. 

I have seen such cases in my experience and it was very difficult to tally the figures and identify the source of variance/mismatch by the users. That's why pointed out this possibility.  

[Anonymous · Accepted solution]

Logically it should not happen. But if it does, it could be because of any authorizations or RLS security feature. For example, if you have country level or product level authorizations set using RLS and if some customers or products are not mapped to respective geographical locations or product category groups, because of your elevated access, it is possible that you are able to see all the data including the unmapped customers or products (remember, DAX creates a blank row to map the unmapped items in relationships), but your colleagues are able to see only data that are mapped to the respective geographies or product groups because the RLS is filtering it.