topic Distinct Count in DAX Commands and Tips

Distinct Count

Abdulrahman_78 — Fri, 23 Oct 2020 15:34:11 GMT

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

Re: Distinct Count

dedelman_clng — Fri, 23 Oct 2020 15:53:45 GMT

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

Re: Distinct Count

themistoklis — Fri, 23 Oct 2020 18:04:55 GMT

Abdulrahman_78

Create the following measure:

Measure = CALCULATE(DISTINCTCOUNT(Table_2[Merge Code]),FILTER(Table_1, Table_1[Sales]>0))

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)

Re: Distinct Count

azaher68 — Fri, 23 Oct 2020 18:07:02 GMT

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

Re: Distinct Count

amitchandak — Sat, 24 Oct 2020 01:20:37 GMT

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