PROBLEM USING PARAMETERS IN POWER BI ONLINE SERVICES

[dbernabefdez]

Hi 

I'm using a parameter to modify the date in a SQL Stored Procedure to get a table in Power BI like in the photo

Everything feels great in Power BI Desktop because I can change the date using the parameter, the Query run as expected and I get all the columns i need for the report. The problem is that when I upload the report to Power BI Services modifying the parameter Online i have "errors" in some visuals.

         

Getting deeper with it I discover it is not a problem with the visuals, instead, there is a problem with a Column that is not processing right (All rows are Blanks) in the model when I use the Online Parameter. I show you the column in the next photos.

Photo of the column in the original model Power BI Desktop                                            

Photo of the column in the downloaded model after changing the parameter online 

Does anyone ever had a problem like this? That column is coming from another dimension table by expanding columns in Power Query

As I was saying in the beginning, when i change the parameter in Power BI Desktop I get everything OK, the problem comes only when changing the parameter in the Online Services

[v-rzhou-msft]

Hi  @dbernabefdez 

Please check whether you enter a right format date value in your online parameter.

Will it show an error to you? It seems that Service get a result from your SQL, but the result is blank.

This may be caused by data value convert from Power BI Service to SQL.

You can try other type of parameter like text or int. And check whether you will get blank result in PBI Service.

You can change parameter in PBI Desktop and republish your report.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[dbernabefdez]

Hi @v-rzhou-msft 

First of all, thanks for answering.

I´ll try what you are saying but I still have doubts with 2 things

First: I´m actually using the parameter as a Text format (VARCHAR in the SQL Sp and Text in the parameter in Power BI), so there should not be problems with the conversion from Date to text or Int, am I right?

Second: Other columns in the model work great and fill all the rows as expected. That column called "DeviceCapacity_Final" is the only that give me "problems" in the model haha. So, that means the parameter is doing the query and working right isnt it? 

That column comes as a result of a expanded column from another table in Power Query, not sure if that is affecting how the parameter is called in Power BI Service.

Best Regards, David.

[v-rzhou-msft]

Hi @dbernabefdez 

Your "DeviceCapacity_Final" is an expanded column from other table, so your connection mode has been transformed to import mode. How did you get the expanded column, by merge or append?

I have a test to check whether expanded column will show error in service.

Name column is varchar() type in SQL.

Manage Parameter:

I will use merge Query1 which table I got from SQL with this local table to expand Study Hours.

Advance Editor of Query1:

Result:

Publish this report to Service, configure gateway and change onlie parameter from B to A.

Refresh the dataset, result is as below.

In my sample the expanded column Studay Hours works well. There may be someting wrong on "DeviceCapacity_Final" column after refresh. Please try to republish your report, don't change your parameter and refresh your dataset. Check whether this column will show blank. If it will show blank after refresh, there may be something wrong in refresh. Power  BI couldn't find the data from data source. Check the private type of two tables, check  "DeviceCapacity_Final" 's datasource.

You can have a test to add this column into your datasource instead of getting it as an expanding column. If it will still show blank in your service, please change the parameter in your desktop and republish your report.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[dbernabefdez]

Hi @v-rzhou-msft 

I used Merge to get data from other tables, not append.

After downloading the model from Power BI Service, I have also discovered that columns are blank in the model 

But in Power Query these fields are filled with Data

   

[v-rzhou-msft]

Hi @dbernabefdez 

Try to refresh the download report. Will empty column show values in data model?

From your screenshot, your column is a Decimal number and there are lots of empty value in your column, try to replace null to 0.

What is the data source of an other table?

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.