Problem with Analysis Services, relationships and PowerBI Desktop

[Anonymous]

Hello everyone,

I am working on a PowerBI dashboard based on an Azure Analysis Services connection and I have issues in drawing my diagram.

My DataWareHouse follows a snowflake schema and all table relationships have many-many cardinality and bi-directional cross filters.

I would like to hide several fields – some of which are keys – for a certain role defined in my analysis services model.

So, when I configured my model as connection in PowerBI, I am not able to show data!

If all fields are shown, my Analysis Services model works fine – but when I hide some fields, I have got that popup error:

It is an unexpected error, and it seems there is an error about a C++ script “xltablevalue.cpp”.

So, Does PowerBI allow us to configure many-many cardinality and hide relationship keys – or I made a mistake inside my schema?

Thanks in advance!

[Anonymous]

In the Microsoft documentation,  I have found that page :

https://docs.microsoft.com/fr-fr/power-bi/guidance/relationships-many-to-many

Do you think I would get better results slotting an intermediary table between my two tables of interests as mentionned in that doc ?

I should try that trick!

[v-stephen-msft]

Hi @Anonymous ,

I found the following description in the blog What Fields to Hide in Your Power BI Solution?.

If there is a field, that you need to bring into the model for any reason, but won’t use it in visualization, then this is a good candidate to be hidden. here are some of these types of fields:

• Fields used in a Relationship
• Fields used to Sort Other Fields
• Fields used in Hierarchy
• Fields used in DAX Calculations, but not in visuals directly

I think the field used in the relationship might be the situation you are facing. The following is a detailed explanation:

In the example below; DimProduct and FactInternetSales are related to each other using ProductKey:

We cannot remove the ProductKey from this model, because we need it to connect the two tables. However, for users, the ProductKey is unnecessary, because they will use other Product columns such as Product Number, Name, Size, Color, etc. So these fields are a good candidate to be hidden from the report view (from both tables).

Sorry, I don't know the language in your picture very well. But after my general research, I found these posts may be helpful to you, please refer to:

https://community.powerbi.com/t5/Service/Couldn-t-load-the-data-for-this-visual-Working-in-Desktop-but/td-p/400301

https://community.powerbi.com/t5/Service/Could-Not-Load-the-Data-from-this-Visual/td-p/431773

https://community.powerbi.com/t5/Service/could-not-load-data-for-the-visual/td-p/52903

Best Regards,

Stephen Tao

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

[Anonymous]

Hi @v-stephen-msft,

Thank you for your big reply!
I apologise for my late reply...

I carrefully read the link you provided and that is not how I would like to hide some fields.
To hide fields to a certain role, I use Visual Studio and through the "Role Manager" menu.

Do you think that the way of hiding fields through PowerBI, and the way through the Visual Studio role manager menu are the same ?

[amitchandak]

@Anonymous , In power bi I doubt you can hide columns from Live connection. Where/when are you getting this error?

[Anonymous]

Hi amitchandak,

Thank you for your reply and so sorry for my late respond!

Here are further elements:

• I draw my model in Analysis Services with Visual Studio and I hide some fields for a role (as I mentionned in my 10 minutes ago post :));
• Then, I set up my connection in my PowerBI Desktop file as an Analysis Services Model. And the error occurs when I would like to plot a table vizualisation with some fields which belong to the table I have hidden some fields.

Hope my explanations were clear enough !

Please let me know if I have to bring some extra elements.