Behavious of "distinctcount" in excel pivot table vs power bi dax measure

[vat2do]

Hi Experts,

I have a quick question.

Is there any difference (with respect to the method of calculatng the results) between distinct count of Excel Pivot table vs distinct count function in power bi DAX?

Any important points need to implement while doing the data trasformation in power bi in order to achieve the same output as Excel Pivot Table distinct count results.

Thanks

[shafiz_p]

@vat2do Litteraly there is no difference between them. Both produces the same result, distinct values. There is difference between Distinct and Unique. Distinct, count all different values only once where as Unique, count values which appears only once. 

To implement distinct count in dax, use DistinctCount function with only one parameter, the column name.
In excel pivot, you need to add the table to the data model.

Just import the right data model in power bi and create a measure Distinctcount = Distinctcount(ColumnName) and place it in the visual and you will get the right result.

Hope this exlained !!

If this answered your question, please mark it as a solution!!

[vat2do]

Thanks @shafiz_p for explining.

I have a an excel table of more thn 30K rows. I have implemented distinct count in excel pivot table and also in power bi DAX but there is a difference in the results. Dax measure is showing more distinctcounts than excel pivot table results. WHich makes it confusing.

Please note :

the data is not changed and no other complex calculations are used at all.

[Anonymous]

Hi @vat2do ,

Can you provide a little bit of your sample data and describe how you are doing the distinct count in excel, also please provide the DAX you are using?Maybe with this information we can help you find the problem.

Best Regards,
Dino Tao

[bhanu_gautam]

 @vat2do  

In Excel, the distinct count in a Pivot Table is calculated by counting the unique values in the specified field.

In Power BI, the distinct count is calculated using the DISTINCTCOUNT function in DAX. This function counts the number of unique values in a column.

This is the measure which is used

DistinctCountMeasure = DISTINCTCOUNT(TableName[ColumnName])