Distinct Count is very slow, is there any alternative

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

// 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

D

[Anonymous]

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

&& __T[ReportReceived] <= __endDate

[Anonymous]

Remove __ from __T. It's a mistake.

Best
D

[Anonymous]

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

[Greg_Deckler]

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