Why is Count and Distinctcount together with Calculate giving a negative number?
I'm writing some formulas that use the Count or Distinctcountnoblank functions, but when I combine them with Calculate to filter based on a condition in the data, it returns a negative number for some of the measures but not all. Whether it's positive or negative seems to vary with the condition applied. See below for an example where I'm trying to output the count of call_id whose call type is "123". How is it even possible for this to return a negative number? How do I make it work properly without having to do a series of IF/THEN clauses? Measure Example = CALCULATE(DISTINCTCOUNTNOBLANK(table[call_id]), table[type]="123")
cumulative count measure per row
hello i need to create a measure that calculates incentive as per below rules Achievement Incentive 1 to 5 count of sales 2$ 6 to 10 count of sales 4 $ 11 to 15 count of sales 6 $ 16 and above count of sales sales 8 $ what this means, if agent has made 15 transactions, he will get incentive of (2$*5) for first 5 transactions, and (4$*5) for next 5 transactions and (6$ *5) the 5 transactions after. so total incentive= 10+20+30=60$ i created below measure = CALCULATE(IF([MNP cumulative count]<=5,[MNP cumulative count]*2,IF([MNP cumulative count]>=6 &&[MNP cumulative count]<=10,[MNP cumulative count]*4,IF([MNP cumulative count]>=11 &&[MNP cumulative count]<=15,[MNP cumulative count]*6,IF([MNP cumulative count]>=16,[MNP cumulative count]*8,0))))) and [MNP cumulative count]= CALCULATE(SUMX(SUMMARIZE('categories trans','categories trans'[AGENT_ID],'categories trans'[AGENT_NAME],'categories trans'[TRANSDATE],'categories trans'[achievement],'categories trans'[Categoty]),[mnp distinct count]), FILTER ( ALLSELECTED('categories trans'), 'categories trans'[TRANSDATE]<=MAX('categories trans'[TRANSDATE]))) my problem is the cumulitve count does not count correctly when agent has 2 transactions in the same day. instead of counting them as 2 it's counting them as 1 also the data i have is as below table agent id agent name transaction value (achievement) date 123 abc 2 1/11/2023 456 def 1 2/11/2023DAX 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.
