Forum Discussion
Need help in DAX, Distinct Count
Hi, I am trying to calculate distinct count for Supplier ID based on Member ID, but it's not working the I want, please find DAX below
DistinctSupplierID =
var __member = T[MemberID]
var __endDate = T[ReportReceived].[Date]
var __startDate = EDATE( __endDate, -6)
var __result =
CALCULATE(
DISTINCTCOUNT(T[SupplierID]),
SELECTCOLUMNS(
FILTER(
T,
T[MemberID] = __member
// __startDate <= T[ReportReceived].[Date]
),
"SuppID", T[SupplierID]
)
)
return
__result
Data:
| SupplierID | MemberID | ReportReceived | DistinctSupplierID | edate |
| 319 | 1 | 31-03-2005 00:00 | 2 | 30-09-2004 00:00 |
| 278 | 1 | 31-03-2005 00:00 | 2 | 30-09-2004 00:00 |
| 214 | 1 | 09-08-2005 00:00 | 1 | 09-02-2005 00:00 |
| 99 | 1 | 27-01-2006 00:00 | 1 | 27-07-2005 00:00 |
| 446 | 1 | 21-02-2006 00:00 | 1 | 21-08-2005 00:00 |
| 446 | 1 | 21-02-2006 00:00 | 1 | 21-08-2005 00:00 |
| 446 | 1 | 13-09-2005 00:00 | 1 | 13-03-2005 00:00 |
| 147 | 1 | 30-04-2005 00:00 | 1 | 30-10-2004 00:00 |
| 112 | 2 | 16-12-2005 00:00 | 2 | 16-06-2005 00:00 |
| 111 | 2 | 16-12-2005 00:00 | 2 | 16-06-2005 00:00 |
| 111 | 2 | 16-01-2006 00:00 | 1 | 16-07-2005 00:00 |
| 111 | 2 | 17-02-2006 00:00 | 1 | 17-08-2005 00:00 |
| 111 | 2 | 19-10-2005 00:00 | 1 | 19-04-2005 00:00 |
| 111 | 2 | 24-08-2005 00:00 | 1 | 24-02-2005 00:00 |
| 111 | 2 | 06-07-2005 00:00 | 1 | 06-01-2005 00:00 |
| 178 | 2 | 30-07-2005 00:00 | 1 | 30-01-2005 00:00 |
| 187 | 2 | 07-01-2005 00:00 | 1 | 07-07-2004 00:00 |
| 109 | 2 | 07-11-2005 00:00 | 1 | 07-05-2005 00:00 |
| 187 | 2 | 26-07-2005 00:00 | 1 | 26-01-2005 00:00 |
Anonymous , OK, you want this:
Column = var __endDate = T[ReportReceived].[Date] var __startDate = EDATE( __endDate, -6) RETURN COUNTROWS( DISTINCT( SELECTCOLUMNS( FILTER( 'T', [MemberID] = EARLIER([MemberID]) && T[ReportReceived].[Date] <= __endDate && T[ReportReceived].[Date] >= __startDate ), "SupplierID",[SupplierID] ) ) )
4 Replies
- Greg_Deckler
Community Champion
Perhaps:
Column = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('T',[MemberID] = EARLIER([MemberID])),"SupplierID",[SupplierID])))The result is 6. I am not expecting this. I want from ant date to last 6 months how many unique Supplier Id.
For above data the distinct supplier Id column should be
2,2,4,3,2,2,5,3,5,5,5,3,3,3,2,3,1,4,2
- Greg_Deckler
Oh, you commented out that line in your code so I thought you didn't want it. It is hard for me to test exactly because I'm in the US so your dates don't convert so well, let me see what I can do about that.