Forum Discussion
Distinct Count
Hi,
I am a new power BI User. i have two data sets (Sales data by customer code by date and Customer Mapping) joint each other with Customer code Key. sample data sets are as under:
Sales Data Set:
Customer Code | Month | Sales |
124011 | September | 1,547 |
140593 | September | -1,014 |
411412 | September | -280 |
Customer Mapping Set:
Customer Code | Merge Code |
124011 | 140593 |
140593 | 140593 |
411412 | 411412 |
i want to calculate the distint counts of Merge codes based on +ve sales. have used the below measure
=CALCULATE(DISTINCTCOUNT('Customer Mapping'[Merge code]),'Sales Data[Sales]'>0)
it should give me the results as '1' but it is showing as '2'
results:
Merge Code | Sales | Distinct Count |
140593 | 533 | 1 |
411412 | -280 | 1 |
it should give a distint count for '411412' as zero but it shows a 1. Kindly advise what am missing here.
Regards,
Abdul Rahman
4 Replies
Hi Abdulrahman_78 -
I cannot replicate your issue using your data and code. Can you share a copy of your PBIX with sensitive data removed?
David
Check your relation as measure looks correct! I replicated your issue and fixed by applying the relation below.
Create the following measure:
Measure = CALCULATE(DISTINCTCOUNT(Table_2[Merge Code]),FILTER(Table_1, Table_1[Sales]>0))OR
You can simply create a column that flags records where sales are greater than 0.
Column = IF(Table_1[Sales]>0,1,0)Then create a measure with the following formula:
Measure = CALCULATE(DISTINCTCOUNT(Table_2[Merge Code]),Table_1[Column]=1)
Abdulrahman_78 , if the issue is not resolved with the above solution, create a new column in sales
Merge code = related('Customer Mapping'[Merge code]) and try with this new column same formula
