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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.