Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

abipanoor

PBI generated query having a lengthy where clause which filter out certain records (Direct Mode)

Submitted by on ‎01-30-2023 08:59 PM

We are creating PBI report with data sources in Direct Mode. Row level security has been implemented through some tables. While viewing the report with data security , we could see a lengthy where clause is included in the PBI generated back end query (Snowflake). It seems the where clause is containing values for each records of the result set, but sometimes here they miss certain records and we couldn't identify why those values are not included. 
We tried using dynamic parameters and able to see the records are coming in the grid, but we couldn't go with parameters as there are some issues with RLS and multi selection. 

Status: Needs Info
See more ideas labeled with:
2 Comments (2 New)
Comments
Anonymous
Not applicable
‎01-31-2023 11:22 PM

Hi @abipanoor 

I can't quite understand your question, can you describe your problem and requirements in detail by sample or screenshot ?

 

Best Regards,
Community Support Team _ Ailsa Tao

 

 

 

abipanoor
Regular Visitor
‎02-02-2023 01:52 AM

image.pngimage (1).png

 

Adding the two screenshots from the reports here. One is in import mode and the other one is direct mode. 
You could see the overall numbers are matching but one row is missing in the direct mode report. This happens when we drill through from one page to this report or when we use the slicers in same page. When we checked the back end query triggered we could see a long where clause unnecessarily included in the query. Adding a small portion from the where clause below,

 

"where (("ITBL"."PERIOD LABEL" = ? and (("ITBL"."PERIOD LABEL" = ? and "OTBL"."Job_Desc" = ?) and (("OTBL"."DIVISION" = ? and "OTBL"."MasterJob_Desc" = ?) and (("OTBL"."Earned Revenue Calculation" = ? and "OTBL"."Job_Status_Desc" = ?) and ("OTBL"."PROJECT MANAGER CODE" = ? and "OTBL"."COMPANY_CODE" = ?))) or ("ITBL"."PERIOD LABEL" = ? and "OTBL"."Job_Desc" = ?) and (("OTBL"."DIVISION" = ? and "OTBL"."MasterJob_Desc" = ?) and (("OTBL"."Earned Revenue Calculation" = ? and "OTBL"."Job_Status_Desc" = ?) and ("OTBL"."PROJECT MANAGER CODE" = ? and "OTBL"."COMPANY_CODE" = ?))))) and ("OTBL"."COMPANY_CODE" in (?, ?, ?, ?))) and ((((("OTBL"."Job_Desc" = ? and "OTBL"."DIVISION" = ?) and (("OTBL"."MasterJob_Desc" = ? and "OTBL"."Earned Revenue Calculation" = ?) and ("OTBL"."Job_Status_Desc" = ? and "OTBL"."PROJECT MANAGER CODE" = ?)) or ("OTBL"."Job_Desc" = ? and "OTBL"."DIVISION" = ?) and (("OTBL"."MasterJob_Desc" = ? and "OTBL"."Earned Revenue Calculation" = ?) and ("OTBL"."Job_Status_Desc" = ? and "OTBL"."PROJECT MANAGER CODE" = ?))) or ((("OTBL"."Job_Desc" = ? and "OTBL"."DIVISION" = ?) and (("OTBL"."MasterJob_Desc" = ? and "OTBL"."Earned Revenue Calculation" = ?) and ("OTBL"."Job_Status_Desc" = ? and "OTBL"."PROJECT MANAGER CODE" = ?)) or ("OTBL"."Job_Desc" = ? and "OTBL"."DIVISION" = ?) and (("OTBL"."MasterJob_Desc" = ? and "OTBL"."Earned Revenue Calculation" = ?) and ("OTBL"."Job_Status_Desc" = ? and "OTBL"."PROJECT MANAGER CODE" = ?))) or ((("OTBL"."Job_Desc" = ? and "OTBL"."DIVISION" = ?) and (("OTBL"."MasterJob_Desc" = ? and "OTBL"."Earned Revenue Calculation" = ?) and ("OTBL"."Job_Status_Desc" = ? and "OTBL"."PROJECT MANAGER CODE" = ?)) or ("OTBL"."Job_Desc" = ? and ........................."

 

It seems this where clause is causing the row missing in the report. Please let me know if you need any other info. Any help would be appreciated. 

Idea Statuses