Can't display the visual

[Anonymous]

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!

[Anonymous]

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!! 

[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.

[Anonymous]

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!

[MFelix]

Are you able to send a mockup?

I don't understand when you reference that the The visual is supposed to show xx data on y-axis and CalenderMonths on x-axis but then you talk about the departments and subdepartments. 

Can you elaborate a litle on the final result you want to achieve.

[Anonymous]

Hi @MFelix. On my page I have a slicer containing four different main departments. All the data on my visuals is then adjusted to the selected department - so there is relations between the different dataset tables and departments. There is a main date table "analysis vwTime" which has all the dates as well as the "CalenderMonthName". In terms of the visual I am talking about, which is a bar chart with "CalenderMonthName" on x-axis and "MyQMS Timeliness" data on y-axis. Every month shows a percentage of how well "MyQMS Timeliness" has performed in that specific month. I either use a date filter to filter  in the last 1 years or in this year. Now the problem occurs when I choose, lets say, department 3, and that department doesn't have data registered for one or more months, then the visual says 'Can't display the visual - unable to convert a value to the data type requested for table "column'. I have tried all the simple options i feel like, such as choosing "Show items with no data", but still no luck. I also tried IF or COALESCE functions. The weird thing is then, that if I just have no department selected in my slicer and instead choose a department, also without data registered in all months, directly from "MyQMS Timeliness" table, no problem - it can still display the visual. So during my detective work I've come to the conclusion that it must be some sort of error in my "Connection Table" and it's relations, that doesn't make it able to show months when there is no data registered.

I hope this clarified it a bit. And again, thank you so much for your time! I really appreciate it.

[MFelix]

How is the [MyQMS Timeliness] calculated you have send out information about the tables for the departments but not for this measure.

It seems weird to get an error of unble to convert a value to the data type requested for table column.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

Thank you for your time and respone @Anonymous . It doesn't seem to work. Could the issue be related to that I have duplicates in my department numbers column in my Connection Table? 

[MFelix]

Hi @Anonymous ,

What is the calculation you are using for the Y-Axis can you share the formula please.

[Anonymous]

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.

[Anonymous]

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?

[Anonymous]

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.