Forum Discussion
Can't display the visual
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!!
Super UserCan 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!
- 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.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.