Reports with parameters in import mode fails to refresh in Power BI service

[meeninthala]

Hi Team,

We are facing an issue with reports with paramters created in import mode are failing in Power BI service with the following exception:

In our example we are building a .pbit file with 2 tables (one datatable and other its corresponding LOV which will be used as a parameter to the datatable) and both lie on the same oracle instance. 

The following are the sample quiries:

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_NAME = " & "'" & Text.From(#"Enter Employee Name") & "'" & "  

In the above query #"Enter Employee Name"  is the parameter that is loaded from an LOV with the following query:

SELECT EMPLOYEE_NAME FROM LOV_EMPLOYEE 

So this LOV column is referred to as the parameter and thus the original datatable is filtered with this parameter in the where clause.

So we have 2 quiries as stated above and we are able to refresh the dataset in desktop level and in Power BI Service we were able to run the reports, the only problem is we couldnt refresh the dataset in Power BI Service.

We had resolved the same exception seen in desktop level by selecting "Always ignore privacy level settings" option under privacy tab of Power BI global settings but we came to know from MSFT Article - Power BI Desktop privacy levels that these settings do not work in the Power BI service.

We are getting this exception very specific to import mode with parameters and is working fine when go with direct query.

We have tried altering various settings in both desktop and gateway levels but we couldnt refresh the dataset succesfully.

Can you please suggest us the way to make these reports refresh succesfully in Power BI Service.

Thanks & Regards,

Mohan Krishna Eeninthala.

[nickyvv]

Hi @meeninthala,

Can you try to merge your queries into one M-query. So add the first LOV-query with the advanced editor into the other query and reference it there?

[meeninthala]

Hi @nickyvv ,

Thank you for your quick reply. 

Actually we already tried the same idea proposed by you by referencing the LOV Query into the main m query all in the advanced editor.

The m query looks as follows:

section Section1;

// Source
shared Employees = let
Source = Oracle.Database("hyd1225d.india", [HierarchicalNavigation=true, 
Query="SELECT * FROM EMPLOYEES"]),
#"Filtered Rows" = Table.SelectRows(Source, each [EMPLOYEE_NAME] = #"Enter Employee Name")
in
#"Filtered Rows";

// LOV or Parameter
shared EMPLOYEE_NAME = let
Source = Oracle.Database("hyd1225d.india", [HierarchicalNavigation=true, 
Query="SELECT EMPLOYEE_NAME FROM LOV_EMPLOYEE"]),
EMPLOYEE_NAME1 = Source[EMPLOYEE_NAME]
in
EMPLOYEE_NAME1;

shared #"Enter Employee Name" = null meta [IsParameterQuery=true, ExpressionIdentifier=EMPLOYEE_NAME, Type="Any", IsParameterQueryRequired=true];

The above query works and we could get data and also refresh the dataset in the power bi service but we have performance issues with it.

Since we have a huge amount of data, we wanted to send the paramter as part of the main query and get the data filtered out in the database level rather than getting filtered in memory on Power BI.

The above query gets the whole data from the database and then applies the filter using the referenced paramter in memory of Power BI which will cause us performance issues.

Hence we wanted to send the paramter in the where clause of the main query and this fails to refresh the dataset in the power bi service.

Thanks & Regards,

Mohan Krishna Eeninthala