Forum Discussion
Can't display the visual
Hello,
I have a clustered column chart visual that can't be displayed - I get the message "Unable to convert a value to the data type requested for table "column". The visual is supposed to show xx data on y-axis and CalenderMonths on x-axis. I found out that issue is because I have some months that doesn't have any registrered data. I tried with this formula "MyQMS_Timeliness_Adjusted = IF(ISBLANK([MyQMS Timeliness]), 0, [MyQMS Timeliness])", but still not working as intended. I also tried using differentert filters on both my date filter and CalenderMonth. The only thing that works is when the date filter is set to a specific time interval where data is registrered in all months. I really want a more sustainable solution, so I can just filter by "Is in the last - 1 years", even though some months have empty data. Any help is much appreciated!
13 Replies
- MFelix
Super User
Hi Anonymous ,
What is the calculation you are using for the Y-Axis can you share the formula please.
It seems the problem only arises when I choose departments based on my own connection table. These are the formulas that I use when filtering between departments in my slicer:
StandardizedName =SWITCH(TRUE(),'Connection Table'[Name] = "alternative name of a sub-department", "regular name for sub-department",'Connection Table'[Name] = "alternative name of a sub-department", "regular name for sub-department",'Connection Table'[Name] = "alternative name of a sub-department", "regular name for sub-department",'Connection Table'[Name] // Default Case)
The above table is to merge the alternative name into the regular sub-department name, so data is registered across both types of name, and to make a slicer just based on the regular name.
And:Group =SWITCH(TRUE(),'Connection table'[StandardizedName] = "name of sub-department" || 'Connection table'[StandardizedName] = "name of sub-department", "name of my main department",'Connection table'[StandardizedName] = "name of sub-department", "name of main department (same name as sub)",'Connection table'[StandardizedName] = "name of sub-department" || 'Connection table'[StandardizedName] = "name of sub-department", "name of main department",'Connection table'[StandardizedName] = "name of sub-department", "name of main department (same name as sub)",'Connection Table'[StandardizedName])
This one is to merge two sub-departments into a main-department. In some cases the sub-department name is the same as the main-department.
And then ofcourse in the same table, I also have a column which connects department numbers with names. Does this help in any way?Thanks for MFelix's concern about this issue.
Hi, Anonymous
I am glad to help you.
Make sure that all names and department numbers in Connection table are correct and that there are no duplicate or incorrect entries.
Why is your DAX formula writing the same thing over and over again? For example:
StandardizedName = SWITCH( TRUE(), 'Connection Table'[Name] = "alternative name of a sub-department", "regular name for sub-department", 'Connection Table'[Name] = "alternative name of a sub-department", "regular name for sub-department", 'Connection Table'[Name] = "alternative name of a sub-department", "regular name for sub-department", 'Connection Table'[Name] // Default Case )Could you please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi MFelix. I cannot see the formula for my MyQMS Timeliness measure, as it is a Direct Query that I have not created myself. But it works fine when connected to the analysis Department table, which is the overall department table. I can't really seem to figure out why it won't convert a "null" value to show in my bar chart. The problem is that my Connection Table cannot show null values. I don't think the problem is in MyQMS timeliness, since I have the same problem in another visual R&U Timeliness (works fine when data in all months, but as soon as a "null" month occurs), there is a problem again. So it is only when there are null values within a month.
Hi Anonymous,
Thanks for your response.
My DAX-formula represents different values/content. Maybe it was badly formulated, but it looks kinda like this:StandardizedName = SWITCH( TRUE(), 'Connection Table'[Name] = "alternative name of a sub-department1", "regular name for sub-department1", 'Connection Table'[Name] = "alternative name of a sub-department2", "regular name for sub-department2", 'Connection Table'[Name] = "alternative name of a sub-department3", "regular name for sub-department3", 'Connection Table'[Name] // Default Case )
I hope this makes sense. All three instances are different departments.Name DepartmentNumber Group StandardizedName StandardizedDepartmentNumber Department1 10001 Main-department1 Department1 10001 Department2 10002 Main-department2 Department2 10002 Department3 10002 Main-department2 Department3 10002 Department4 10002 Main-department2 Department2 10002 Department5 10003 Main-department3 Department5 10003 Department6 10003 Main-department3 Department6 10003 Department7 10003 Main-department3 Department5 10003 Department8 10003 Main-department3 Department6 10003 Department9 10004 Main-department4 Department9 10004 This is what my Connection Table looks like. The reason for "StandardizedName" is because three of the departments are being called with another name in their specific dataset, but is actually the same. For example Department4 should be under the same name as Department2 in my slicer, since it is the same department. I hope this makes sense. Why I have StandardizedDepartmentNumber, I'm not really sure now that I look at it, since it just replicates DepartmentNumbers. I hope this makes sense.
When I now choose Group - Main-Department2 from my slicer it has the sub-choices Department2 and Department3. For some reason, whether I choose Main-Department2, or just one of the two, Department2 or 3, I am only able to have my visual displayed if data is registered in all of the last 12 months for example. If there is a month withoung registrered data I get the error 'Couldn't load the data for this visual - unable to convert a value to the data type requested for table "column'. If I choose a main-department which has data registered in all of the last 12 months, no problem at all. It only occurs when there is missing data. All of my columns are formatted as "Text" and I've tried changing the DepartmentNumbers to another Data Type, but no difference. Now to the relatioships. My Connection Table is connected to the "Name" column, where name is DepartmentNumbers, in the Overall Department Table, which has data for all departments. This relationship is a Many-to-Many with a Both in Cross Filter Direction. I have experimented here as well, nothing seems to work. And then the "Overall Department Table - Name" column is connected to the "Owning Department" (this is again also department numbers) column in my "QualityManagement" table which contains all the data for this specific visual. This is a Many-to-One relationship with Single Cross Filter Direction (arrow pointing towards "QualityManagement"). I cannot change in this relationship as it is greyed out. And of course my "QualityMangament" table is connected to a Date table. Really looking forward to your answer!!Hi, Anonymous
In Power BI, missing data can lead to visualization errors. You can use DAX functions such as IF or COALESCE to handle missing values.
You can refer to the following DAX:StandardizedName = SWITCH( TRUE(), 'Connection Table'[Name] = "alternative name of a sub-department1", "regular name for sub-department1", 'Connection Table'[Name] = "alternative name of a sub-department2", "regular name for sub-department2", 'Connection Table'[Name] = "alternative name of a sub-department3", "regular name for sub-department3", COALESCE('Connection Table'[Name], "Unknown") // Default Case )
You should also try to avoid many-to-many relationships. You can try to change the relationship between Connection Table and Overall Department Table to one-to-many. If you cannot change it directly, consider creating an intermediate table to simplify the relationship.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- MFelix
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.Hi MFelix. Thanks for your respone. Unfortunately it is not possible for me to share my PBIX file as it contains sensitive data. I appreciate your time and effort to help me!
