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.
Hello Community!
Im fairly new with DAX, and I am dying to get help with this problem!
I am trying to find out:
1. Distinct count of the customers who purchased Product A, OUTSIDE their subscription time.
2. Count of the purchases (Product A) they made OUTSIDE their subscription time
My data model is very simple:
Customer table (relation 1 - * w/ CustomerID) Sales table (relation * - 1 w/Date) Date Table
Customer table (relation 1 - * w/ CustomerID) Subscriptions table
One customer can have two different subscriptions on and off (SubscriptionA, SubscriptionB), which have starting date and ending date. Some are still active and they dont have an ending date.
At any time they can also make purchases (Product A) and Im interested to find out only those which have been made outside these subscription times. Note, that some customers have never been subscribers, only bought Product A. I need these as well.
Hope I was clear with me explanation and someone has time to help with this 🙂
Solved! Go to Solution.
You could create a measure like
Purchases outside subscriptions =
VAR CustWithSub =
FILTER (
VALUES ( 'Customer'[Customer ID] ),
NOT ISEMPTY ( RELATEDTABLE ( 'Subscription' ) )
)
VAR CustWithoutSub =
FILTER (
VALUES ( 'Customer'[Customer ID] ),
ISEMPTY ( RELATEDTABLE ( 'Subscription' ) )
)
VAR CustWithDates =
GENERATE (
CustWithSub,
VAR CustID = 'Customer'[Customer ID]
VAR SubAStart =
LOOKUPVALUE (
'Subscription'[Start date],
'Subscription'[Customer ID], CustID,
'Subscription'[Type], "Subscription A",
MAX ( 'Date'[Date] )
)
VAR SubAEnd =
LOOKUPVALUE (
'Subscription'[End date],
'Subscription'[Customer ID], CustID,
'Subscription'[Type], "Subscription A"
)
VAR SubBStart =
LOOKUPVALUE (
'Subscription'[Start date],
'Subscription'[Customer ID], CustID,
'Subscription'[Type], "Subscription B",
MAX ( 'Date'[Date] )
)
VAR SubBEnd =
LOOKUPVALUE (
'Subscription'[End date],
'Subscription'[Customer ID], CustID,
'Subscription'[Type], "Subscription B"
)
VAR SubADates =
DATESBETWEEN ( 'Date'[Date], SubAStart, SubAEnd )
VAR SubBDates =
DATESBETWEEN ( 'Date'[Date], SubBStart, SubBEnd )
RETURN
UNION (
EXCEPT ( VALUES ( 'Date'[Date] ), SubADates ),
EXCEPT ( VALUES ( 'Date'[Date] ), SubBDates )
)
)
VAR NumPurchasesWithoutSub =
CALCULATE ( COUNTROWS ( 'Sales' ), CustWithoutSub )
VAR NumPurchasesWithSub =
CALCULATE (
COUNTROWS ( 'Sales' ),
TREATAS ( CustWithDates, 'Customer'[Customer ID], 'Date'[Date] )
)
RETURN
NumPurchasesWithoutSub + NumPurchasesWithSub
to give you the number of purchases. And then create a measure like
Num customers =
COUNTROWS (
FILTER (
VALUES ( 'Customer'[Customer ID] ),
[Purchases outside subscriptions] > 1
)
)
You could create a measure like
Purchases outside subscriptions =
VAR CustWithSub =
FILTER (
VALUES ( 'Customer'[Customer ID] ),
NOT ISEMPTY ( RELATEDTABLE ( 'Subscription' ) )
)
VAR CustWithoutSub =
FILTER (
VALUES ( 'Customer'[Customer ID] ),
ISEMPTY ( RELATEDTABLE ( 'Subscription' ) )
)
VAR CustWithDates =
GENERATE (
CustWithSub,
VAR CustID = 'Customer'[Customer ID]
VAR SubAStart =
LOOKUPVALUE (
'Subscription'[Start date],
'Subscription'[Customer ID], CustID,
'Subscription'[Type], "Subscription A",
MAX ( 'Date'[Date] )
)
VAR SubAEnd =
LOOKUPVALUE (
'Subscription'[End date],
'Subscription'[Customer ID], CustID,
'Subscription'[Type], "Subscription A"
)
VAR SubBStart =
LOOKUPVALUE (
'Subscription'[Start date],
'Subscription'[Customer ID], CustID,
'Subscription'[Type], "Subscription B",
MAX ( 'Date'[Date] )
)
VAR SubBEnd =
LOOKUPVALUE (
'Subscription'[End date],
'Subscription'[Customer ID], CustID,
'Subscription'[Type], "Subscription B"
)
VAR SubADates =
DATESBETWEEN ( 'Date'[Date], SubAStart, SubAEnd )
VAR SubBDates =
DATESBETWEEN ( 'Date'[Date], SubBStart, SubBEnd )
RETURN
UNION (
EXCEPT ( VALUES ( 'Date'[Date] ), SubADates ),
EXCEPT ( VALUES ( 'Date'[Date] ), SubBDates )
)
)
VAR NumPurchasesWithoutSub =
CALCULATE ( COUNTROWS ( 'Sales' ), CustWithoutSub )
VAR NumPurchasesWithSub =
CALCULATE (
COUNTROWS ( 'Sales' ),
TREATAS ( CustWithDates, 'Customer'[Customer ID], 'Date'[Date] )
)
RETURN
NumPurchasesWithoutSub + NumPurchasesWithSub
to give you the number of purchases. And then create a measure like
Num customers =
COUNTROWS (
FILTER (
VALUES ( 'Customer'[Customer ID] ),
[Purchases outside subscriptions] > 1
)
)
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |