Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a report with Text records (user comments) that are filtered by another control in the same report. The Text records filter in Power BI desktop, but not when using Power BI service. I am using a Direct Query to a SQL DB. I am getting the "Can't display the Visual, See details"
Any hints from the community would be appreciated.
Solved! Go to Solution.
Hi @rodrigor,
A change was made recently to send better Direct Query to SQL Server data source (and may be other).
But these queries use a function that is not compatible with text/ntext/image data type.
TEXT, NTEXT data types are deprecated. TEXT is non-unicode which can cause all kinds of problems
Please try to change data type to VarChar(Max) or NVarChar(Max).
Best Regards,
Qiuyun Yu
Hi @rodrigor,
As the report get data from SQL Server database, it requires gateway after publish to service. Have you created a SQL Server data source under data gateway and configure the dataset to use that data gateway? For more information, see: Manage your data source - SQL Server.
Best Regards,
Qiuyun Yu
thanks Qiuyun Yu. Is it actually necesary to do this. Other reports and controls are working fine without the gateway. It is just the text field that is causing the report to break. More details in my reply to Guavaq.
Hi there, do you have any more details around the error?
Hi guavaq, I oversimplified the report to make the trouble shooting easier. I created one report with an ID (whole number) field and a Comments (Text) field, both from a Azure SQL DB table. The report can filter the Comments using the IDs if I use Power BI desktop. The report won't work using the Service.
From the Power BI service
From Power BI desktop
If I remove the Comment (text) field from the report, and just add other non-text field to it (sentiment-Numeric, feeling-Numeric), the report works with any problems. So there is something with wrong displaying text fields in the Power BI service. I actually created a new Calculate column to clean and reduce the number of characters in the Comments field, and still the report won't display the text in the power Bi Service.
Hi @rodrigor
Is there a relationship between the data?
And in your second screenshot there is nothing selected in terms of filtering using the slicer.
No relationships in between the data. One single table.
The second screenshot (desktop) has nothing selected, but if I select any filter from ID it works. The first screenshot, as soon as I filter from ID, I got that error message.
Hi @rodrigor,
A change was made recently to send better Direct Query to SQL Server data source (and may be other).
But these queries use a function that is not compatible with text/ntext/image data type.
TEXT, NTEXT data types are deprecated. TEXT is non-unicode which can cause all kinds of problems
Please try to change data type to VarChar(Max) or NVarChar(Max).
Best Regards,
Qiuyun Yu
These don't work for NVARCHAR either. Text boxes cannot be assigned a value unless Q&A is on. There is no apparent way to do this in Power BI Desktop.
A basic text visual is needed that can display a multi-line, left-aligned string (for example, a postal address). This cannot be done in Power BI without workarounds. This is why I am looking at alternatives to Power BI.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.