Error while using POWER BI Data Set Hierarchy in Excel pivot

[Vemu2022]

Hello Everyone,

we created a data set and published to PBI service and given the access to users to connect in excel to do pivoting,

they asked us to create date Hierarchy and it should work like any other hierarchy like below 

i created a date hierarchy in power bi when i tried to use i am getting below error 

could you please suggest me what i need to do to make the hierarchy work in excel 

Thanks 

Vemu H

@PowerBIDesktop @Excelpivot

[ovidiubaciu]

@Vemu2022 were you able to find a solution? I have the same issue.

[v-luwang-msft · Accepted solution]

Hi @Vemu2022 ，

cannot query internal supporting structures for column <calculate column> because they depend on a column, relationship, measure  that is not processed. please refresh or recalculate the model.
According to above error message, it might be caused by the calculated column formula. Please refer to below similar thread:
Calculated Column Error
Error about using a concatenated calculated column in a SSAS Tabular model

It might also be caused by the processing method, please refer to below blog:
https://biamir.wordpress.com/2015/12/15/fix-cannot-query-internal-structures-for-column-because-they...

And similar refer:

https://stackoverflow.com/questions/65073314/analysisservices-cannot-query-internal-supporting-struc... 

https://biamir.wordpress.com/2015/12/15/fix-cannot-query-internal-structures-for-column-because-they... 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Lucien

[wannabwhiz]

Easier solution:

- Open the model in Power BI Desktop and manually refresh the data.
- After doing this, republish the dataset.
- Reconnect to the semantic model from Excel and insert the pivot table again.