DAX INNER JOIN query takes too long time to execute

[Jasonjm]

Hello everyone,

I apologize in advance for the newbie question.

I created a simple dashboard that connects to Dynamics 365 Dataverse and queries accounts based on associated connections. Everything works fine but my query takes way too long to run (around 20 seconds). In the end, it is all about an INNER JOIN between the account and connection tables.

The data model is quite simple:

I have created a 1:N relationship between account and connection tables:

Am I right to create this relationship directly in the data model in order to avoid performing a NATURALINNERJOIN in the DAX query?

This is my DAX measure formula, that counts the number of account records where the connection role equals "RoleX":

CountRoleXAccounts = 
CALCULATE(
    COUNTROWS('account'),
    FILTER(
        'connection',
        'connection'[record1rolename] = "RoleX"
    )
)

This query works fine but takes about 20 seconds to return the result and it's less than 1000 records.

I imagine that this is not the right way to do it, and guess that the entire dataset of connection entity (around 15k records) is fetched several times.

Could you give me your advice please?

PS: This would be my SQL query equivalent

SELECT 
  COUNT(*) 
FROM 
  account 
  INNER JOIN connection ON account.accountid = connection.record1id 
WHERE 
  connection.record1rolename = 'RoleX'

This last query runs very quickly through SQL Server Management Studio.

[lbendlin]

Those queries are not equivalent, especially when you have multiple record rows for each account.

You may want to look into SUMMARIZECOLUMNS or GROUPBY and then filter by role name later.

Use DAX Studio to examine your original query and then refactor based on your findings.

If you like more help, please provide sample data that fully covers your issue.