topic Re: Distinct Count is very slow, is there any alternative in DAX Commands and Tips

Distinct Count is very slow, is there any alternative

Anonymous — Sat, 07 Mar 2020 02:45:09 GMT

Hi,

I would like to calculate DistinctCount(SupplierID) over the previous 6 months for the same MemberID. For the same, I wrote following DAX;

DistinctSupplierID =

CALCULATE(

DISTINCTCOUNT('Sales&Orders'[SupplierID]),

DATESINPERIOD(

'Sales&Orders'[ReportReceived].[Date],

LASTDATE('Sales&Orders'[ReportReceived].[Date]),

-6,

MONTH),

FILTER(

'Sales&Orders',

'Sales&Orders'[MemberID]= EARLIER('Sales&Orders'[MemberID])

)

Unfortunately its taking too much time. Is there any alternative to make it faster.

Thank you in advance.

Re: Distinct Count is very slow, is there any alternative

Greg_Deckler — Sat, 07 Mar 2020 03:40:56 GMT

Well you can use COUNTROWS coupled with DISCTINCT or VALUES. You could eschew time intelligence functions in favor of simple filtering. Hard to test anything without sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Distinct Count is very slow, is there any alternative

Anonymous — Sat, 07 Mar 2020 04:08:56 GMT

Please find data;

SupplierID	MemberID	ReportReceived
319	1	31-03-2005 00:00
278	1	31-03-2005 00:00
214	1	08-09-2005 00:00
99	1	27-01-2006 00:00
446	1	21-02-2006 00:00
446	1	21-02-2006 00:00
446	1	13-09-2005 00:00
147	1	30-04-2005 00:00
112	2	16-12-2005 00:00
111	2	16-12-2005 00:00
111	2	16-01-2006 00:00
111	2	17-02-2006 00:00
111	2	19-10-2005 00:00
111	2	24-08-2005 00:00
111	2	07-06-2005 00:00
178	2	30-07-2005 00:00
187	2	01-07-2005 00:00
109	2	11-07-2005 00:00
187	2	26-07-2005 00:00

Re: Distinct Count is very slow, is there any alternative

Anonymous — Sat, 07 Mar 2020 10:33:21 GMT

// T is your table's name // calc column without the use of context transition # DistinctSupplierID = var __member = T[MemberID] var __endDate = T[ReportReceived] var __startDate = edate( __endDate, -6 ) var __result distinctcount( selectcolumns( filter( T, T[MemberID] = __member && __startDate < T[ReportReceived] && __T[ReportReceived] <= __endDate ), "SuppID", T[SupplierID] ) ) return __result

Best

Re: Distinct Count is very slow, is there any alternative

Anonymous — Sat, 07 Mar 2020 12:49:23 GMT

What is the second && statement can you please explain. Anonymous

&& __T[ReportReceived] <= __endDate

Re: Distinct Count is very slow, is there any alternative

Anonymous — Sat, 07 Mar 2020 20:43:29 GMT

Remove __ from __T. It's a mistake.

Best
D

Re: Distinct Count is very slow, is there any alternative

Anonymous — Sun, 08 Mar 2020 03:41:06 GMT

It's not working, I am getting error The syntax for 'distinctcount' is incorrect.