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 all,
My data model is composed , amongst other tables, by an Orders table (facOrder) and a dimensional customer table (dimCustomer).
These 2 tables are linked by customerID key.
I wrote a DAX metric to retreive the cumulative total of customers that had, at least one order registered in the facOrder table, like this:
CumUsers =
// metric to calculate the cumulative number of customers since theyr first order
VAR CustomerOrders =
SUMMARIZE(
facOrders,
facOrders[CustomerID],
"FirstOrderDate",
MIN(facOrders[CreationDate])
)
VAR SelDate = MAX( dimDates[DateID] )
VAR CountCustomers = COUNTROWS( FILTER( CustomerOrders, [FirstOrderDate] <= SelDate ) )
RETURN
IF( CountCustomers = BLANK(), 0 , CountCustomers )
What's happening, is that this metric is not retreiving an acumulated total, but is, instead, calculating the total for a given selected (by the filter context) month. And, btw, I'm trying to plot a chart that renders monthly figures.
Before you ask for data: i'm working with sensitive data, so if you want something to test, please use AdventureWorks database .
Thanks in advance 🙂
Solved! Go to Solution.
@SergioTorrinha , Not very clear.
Try like
calculate(counrows(facOrders), filter(allselcted(facOrders) , facOrders[CreationDate] <=Max(facOrders[CreationDate])))
or
calculate(counrows(facOrders), filter(allselcted(facOrders) , facOrders[CreationDate] <=Max(facOrders[CreationDate]) && facOrders[CustomerID] <=Max(facOrders[CustomerID])))
@SergioTorrinha , Not very clear.
Try like
calculate(counrows(facOrders), filter(allselcted(facOrders) , facOrders[CreationDate] <=Max(facOrders[CreationDate])))
or
calculate(counrows(facOrders), filter(allselcted(facOrders) , facOrders[CreationDate] <=Max(facOrders[CreationDate]) && facOrders[CustomerID] <=Max(facOrders[CustomerID])))
Hi @amitchandak !
Thank you for your feedback.
I managed to make it work properly, with the following adaptation of your first DAX:
a_test =
CALCULATE(
DISTINCTCOUNT(facOrders[CustomerID]),
FILTER( ALLSELECTED(facOrders) , facOrders[CreationDate] <= Max(dimDates[DateID])) //Max(facOrders[CreationDate]))
)
Thank you for your help! 🙂
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |