Distinct count VS Countrows(values(....))

[admin_xlsior]

Hi guys,

Can we replace DistinctCount() to CountRows(Values(..)) ? Which one is quicker ?

I have a measure like this :

OrderCount = CALCULATE(DISTINCTCOUNT('Order analysis'[order Id]),
FILTER('Order analysis', ('Order analysis'[WIP open] <= LASTDATE('WIP Date'[Date])
&& 'Order analysis analysis'[WIP closed] >= FIRSTDATE('WIP Date'[Date]))))

Which apperantly taking very long processing time and sometime out of memory.

Thanks,

[V-lianl-msft]

Hi @admin_xlsior ,

Many factors may affect the performance of power Bi, such as large data volume, data model and complex DAX expression.

You can refer to the following article to optimize the performance of power bi.

https://www.sqlbi.com/articles/capturing-power-bi-queries-using-dax-studio/

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

'Order analysis'[order Id] is the primary key then you can use count or countrows. 

[admin_xlsior]

Hi guys,

so currently I changed my measure to be like this :

OrderCount =
VAR TmpOrder = ADDCOLUMNS(
SUMMARIZE(
FILTER('Order analysis', ('Order analysis'[WIP open] <= LASTDATE('WIP Date'[Date])
&& 'Order analysis'[WIP closed] >= FIRSTDATE('WIP Date'[Date]))
),
'Order analysis'[Order Id])
,"tmp",1
)

RETURN SUMX(TmpOrder, [tmp])

And it looks faster. Is it correct to use that ?

Thanks,