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 would like to calculate DistinctCount(SupplierID) over the previous 6 months for the same MemberID. For the same, I wrote following DAX;
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 |
// 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
What is the second && statement can you please explain. @Anonymous
&& __T[ReportReceived] <= __endDate
It's not working, I am getting error The syntax for 'distinctcount' is incorrect.
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
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 | |
19 | |
18 |