PowerBI report displays wrong values.

[aszentmihalyi]

I have a report, with configured Row Level Security rule. In most cases it works well, but for some users the report is get messed up.

The Row Level Security rule is simple: [UserName] = USERNAME() . For testing purposes, I set it [UserName] = „the name of the user” - but it gives the same result.

//We have about 20K rows, with RLS filtering about 13K rows should be displayed.

For the other users that have 3-5k rows the report works well, but I couldn’t find any relations between this behavior and the amount of data.

I don’t know if it is important, but other users – who have access to 3-5K rows – the report works well. In spite of this I cannot say that the count of the rows is related to this issue.

Scenarios:

1. Without any filtering the report is OK.
2. Without RLS filtering, but setting the same filter options in the page level filters, the report is OK.
3. With RLS filtering (even with USERANAME() or constant string) the report is messed UP.
4. With RLS filtering and setting the same filter options in the page level filters, the report is messed UP.
5. With RLS filtering, but without calculating the Total value, the column looks good, but another column gets messed UP.
6. With RLS filtering, but setting page filters to show only a few rows, the report looks OK.

The data behind the report is generated, in some rows the strings are long (1-2k characters) and the type of numbers are decimal(38,19). I gave a shot to convert all the string to varchar(255) and all the numbers to integer, but nothing changed.

Could you please help me to solve this problem?

thanks

[v-jiascu-msft]

Hi @aszentmihalyi,

That's weird. It couldn't work in your scenario but all depend on your data. 

1. Within Power BI Desktop, username() will return a user in the format of DOMAIN\User and userprincipalname() will return a user in the format of user@contoso.com. It doesn't match the source data as we can see from the filter you used. It could always return 0 rows.

2. Why are the second picture different from the third one as we can't see any difference?

3. Maybe you can try this rule.

if(username()="domain\username", [UserName] = "mcomo",blank())

4. Could you please share a dummy sample?

Best Regards!

Dale

[aszentmihalyi]

Hi Dale

Thanks for responding my question!

1. I didn’t mention that we use this report as a web embedded report and we can simply add the username as Row Level Security parameter. In my PowerBI desktop application there is an option to give an arbitrary username. I think this part works as it should.
   username settings
   
2. The second and third picture is different, because  the Grand Total option for the first numeric column is turned off. That’s the only change I made. After this, the first numeric column looks good, but the third got messeg up.
   In addition, the report shows absolutely insane numbers:
   insane numbers
   

   Although, the grand total is OK.

3. Thanks for your advice but because of embedding the report, we do not need domain names.
4. We use DirectQuery in the report and we have sensitive data, so unfortunately it’s not easy to share the report…
   I’m thinking about my possibilities.

thanks,

Akos