- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
"Contains blank values and this is not allowed" error - only in service not desktop
I have suddenly started getting this relationship error when refreshing in the PBI service but I can refresh without any issues in desktop.
A couple of points:
1. This field in question has no relationships with any other tables
2. This field has no blank values as the error suggests
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok after hours of playing around I finally found the solution but still don't understand why this was happening since there was no relationship with this field.
All I had to do is go to the model view, select the field in question -> Properties -> Change "Is nullable" to Yes.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I've had this issue for the past hour, just found a solution.
In my query I added a step to remove blank values (specifically, I de-selected <null>).
This surprised me as visibly I didn't have any blanks, but somehow the query had found some.
This is working better now.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok after hours of playing around I finally found the solution but still don't understand why this was happening since there was no relationship with this field.
All I had to do is go to the model view, select the field in question -> Properties -> Change "Is nullable" to Yes.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for posting! Worked for me in resolving similar issue
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@jakeryan56 Thanks for the solution, I'll add this one to my list. What was your data source that this was set to 'no' by default? Or do you think it's possible you might have changed it? Generally it defaults to 'yes' for most columns. I'm wondering if perhaps there used to be a relationship using this column?
Please @mention me in your reply if you want a response.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @jakeryan56 ,
Glad to hear that you have solved the issue. Please accept your reply as a solution so that people who may have the same question can get the solution directly.
Then, for the "Is nullable" property, please check if this post could help you understand it better: The Is Nullable Column Property In Power BI.
Best regards
Icey
If this post helps, then consider Accepting it as the solution to help other members find it faster.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Another update. I tried deleting the field in question and now I am getting the same error but on a different column.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I prefer the graphical view. Can you use the latest desktop version, creat a new page in the model view. Add the table in question to this new view. Right click, then add related tables. Finally enable show key columns at top of table and then send a screenshot of that please. And @ me in replies. Notifications aren't working well so I might lose the thread otherwise.
Please @mention me in your reply if you want a response.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Allison.
I've done all of those things (I did realise I was using an older version) but still hasn't made any difference. I've also tried publishing in a new workspace and same issue still
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@jakeryan56 is the report published in a shared workspace in service? Is there any possibility that the data model of the desktop is different to the service version?
Can you try to republish the desktop report since it can refresh and see if that helps?
If not, you may actually need to delete the dataset in service and republish. It has been over a year since I've tried but I used to have issues with changing the data model and not deleting the old version first. Some things like new columns wouldn't pull through to the service.
Please @mention me in your reply if you want a response.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yeah it is in a shared workspace but I already tried publishing to a fresh workspace and I get the same error. It's obviously something in desktop that I can't see and being pushed into the service. Any ideas? Settings, etc?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@jakeryan56 , can you share the data model diagram
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
As you can see here this table "ClickposLineItems_Agg" does not have any active relationships using the field in question
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@jakeryan56 , You have create a 1- Many relationship and you are getting null value in 1 side of the table. This can be master / dimesnion table
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
No as I mentioned I have NOT created any relationships on this field. I have triple checked and this is not the case at all.
If this was true it wouldn't refresh in desktop
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
Anonymous
| 07-08-2024 03:25 AM | ||
02-08-2024 05:06 PM | |||
07-22-2024 04:24 AM | |||
11-05-2023 05:45 AM | |||
07-12-2024 10:01 AM |
User | Count |
---|---|
113 | |
80 | |
55 | |
54 | |
44 |
User | Count |
---|---|
168 | |
114 | |
74 | |
61 | |
53 |