Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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 |
Solved! Go to Solution.
@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]
)
)
)
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
@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]
)
)
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |