Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
_Peter_
New Member

Combination Direct Query, "Security filter in both directions" and a datetime column issues

In a Direct Query report on an Azure SQL database I configured relationships from USER to ORGANISATION (n:1, filter in both directions and "Security filter in both directions") and from ORGANISATION to ERRORS (1:n). I also configured RLS for the user id to filter the USER table.

 

When I put a filter on user in the the report it work fine, but when I use the "Show as" feature to filter on [USER_ID] = USERNAME() no data is shown in visualizations containing the datetime column.

 

Looking at some of the generated SQL I see with filtering on a user in the where clause:

WHERE  ( [t1].[USER_ID] = N'TWS-OrgUser1@cgi.com' )


 

With "Show as" for a specific user, 2 queries are executed. 1 with a similar where clause selecting all but the Organisation_id column and a second one with a very large WHERE clause containing every column and value from the first. This one returns 0 rows, because of the clauses on the Date/Time/Timezone column like:

[t2].[Log timestamp] = CAST( '20180706 11:10:23' AS datetime)

The issue disappears if I change the type of the column to text, but I want it to be a date time column for filtering.

 

My assumption is that the security filter in both directions causes the very large where clause including the filtering on date/time/timezone. Is this a bug or how can I prevent this?

 

1 ACCEPTED SOLUTION
_Peter_
New Member

Finally determined that the cause for the second query was a column containing the organisation name from the ORGANISATION table. When removing this column from the report or including the organisation_id the data is correctly filtered. That is a sufficient workaround for us.

View solution in original post

2 REPLIES 2
_Peter_
New Member

Finally determined that the cause for the second query was a column containing the organisation name from the ORGANISATION table. When removing this column from the report or including the organisation_id the data is correctly filtered. That is a sufficient workaround for us.

Hi

Problem seems to be solved, thank you for sharing.

 

Best regards

maggie

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors