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!

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

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

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

 

 

 

Comments
v-yetao1-msft
Community Support
Status changed to: Needs Info

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

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.