Dataverse Connector Limit for Import vs. Direct Query

[Ksven]

Hi lovely Community,

currently I'm in trouble with my dataverse data model in power bi.

I've a "huge" table with lot of columns and about 90K rows. If I query this table as dual or import mode I receive the following error:

If theres a limit for the connector, fine for me, so I've changed it to direct query.

But now I've some related tables to my huge one and if I wanna filtering it my measures are crashing with this error:

Without filtering of related tables, Measures working fine. With filtering not. 

So I'm in a devils circle 😮

Any suggestions?

Thank you in advance!

BR,

Sven

[mahoneypat · Accepted solution]

Thanks for sharing the model pic, which shows it isn't a query issue but a model/DAX one.  The use of bi-directional filters is a discouraged practice, and you should consider using TREATAS() or CROSSFILTER() in your measure.  This article discusses both.

Relationships in Power BI and Tabular models - SQLBI

Regards,

Pat

[mahoneypat]

How were you pulling in the CRM data (in import mode)?  The Dataverse connector?  90K rows should be no problem.  How many columns?  In any case, have you tried the approach in this article to return only the columns you need (i.e., $select=...) ?

How to retrieve OData v4 fast in Power BI - Microsoft Dynamics CRM Community

Pat

[Ksven]

Hi @mahoneypat ,

yes I have tried Import Modus via Dataverse Connector.

In my table are arround 100 columns, so I guess records size limit is reached already with over 90K records.

Already have tried Odata / Common Data Service (Legacy) Connectors, but here I haven't name columns like of Choice Columns or related tables for record. I would have to expand or replace my IDs and this seems to me not performant.

I guess the other error message regarding Table.Join is cause of my Datamodel:

I have to use the one Table as Import Query cause its for 4 ID columns to "pivot" them -> Not allowed with Direct / Dual Query.

I'm wondering why my Measures are working without setting a filter (calculation regarding attributes from table "Händler" = "Dealer" ) and not with a Filter of the same attribute..

Any ideas how to set up better?

BR,

Sven