Distinct Count

[Abdulrahman_78]

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

[amitchandak]

@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

[themistoklis]

@Abdulrahman_78 

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)

[dedelman_clng]

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

[azaher68]

Check your relation as measure looks correct! I replicated your issue and fixed by applying the relation below.