Column names to single column

[Lazuriii]

Hello,

The title might be misleading, but I try to explain my problem with a simple example, since I can't share the actual data. My dataset is connected to another dataset through DirectQuery. I don't have access to the original dataset due to company restrictions.

In the data, there is a table called 'Cost center' that describes the cost center hierarchy within the company. The cost centers are divided into 10 levels (Level 0, Level 10, ... , Level 90), where Level 90 is the "top level".

Here's an example:

Level 90	Level 80	Level 70
USA	Administration	Wages
Canada	Operations	Production

Now, the problem is that I want to create a slicer that has each level as a dropdown so that every Level can be seen when opening the dropdown.

Example: Cost Center level -> Level 90/Level 80/Level 70 (Now it is USA/Canada as the first choices)

I have been able to create a calculated table within the dataset that solves this problem, but unfortunately DirectQuery and calculated tables do not work together and I get an error stating that the dataset cannot be refreshed. Is there any way around this? The report currently uses bookmarks to create dropdown menus, but they are quite heavy to operate and make the report a bit clumsy to use.

[whitch]

Someone else in the company must manage the original dataset.  Ask them to add the column for you.  If you are not friends with this person/team, you will not be able to get anything done.  I don't just mean this specific issue: you will need this person/team many times in future too.  Escalate through your management structure if needed.  You literally cannot do your job without this support.

[Lazuriii]

Luckily this is the only dataset that I need, which is managed by other people. But you are right that this is something that might need to be addressed.