cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidBernabeQBI
Frequent Visitor

PROBLEM USING PARAMETERS IN POWER BI ONLINE SERVICES

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

 

DavidBernabeQBI_0-1621234267996.png

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.

 

DavidBernabeQBI_4-1621236207608.png         DavidBernabeQBI_2-1621234832541.png

 

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                                            

                                                                                  

DavidBernabeQBI_0-1621235157345.png

                         

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

 

DavidBernabeQBI_2-1621235187380.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

DavidBernabeQBI_0-1621237271852.png

 

 

 

5 REPLIES 5
RicoZhou
Community Support
Community Support

Hi  @DavidBernabeQBI 

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. 

Hi @RicoZhou 

 

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.

Hi @DavidBernabeQBI 

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.

1.png

Manage Parameter:

2.png

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

3.png

Advance Editor of Query1:

4.png

Result:

5.png

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

Refresh the dataset, result is as below.

6.png

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. 

Hi @RicoZhou 

 

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 

 

DavidBernabeQBI_0-1621522645394.png

 

DavidBernabeQBI_1-1621522694597.png

 

But in Power Query these fields are filled with Data

DavidBernabeQBI_2-1621522780299.png   DavidBernabeQBI_3-1621522796212.png

 

 

 

 

Hi @DavidBernabeQBI 

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. 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors