DAX INNER JOIN query takes too long time to execute
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.
