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.
I have a measure to count customers based on some criteria. Basically I want to find the excisting customer of product A, who started to buy product B and C.
Measure =
VAR _custA =
SUMMARIZE (
FILTER (orders,orders[product]= "A"),
orders[customer ID],
"Sales", SUM (orders[sales]))
RETURN
CALCULATE(
DISTINCTCOUNT( orders[customer ID] ),
FILTER ( _custA, [customer ID] = orders[customer ID]),FILTER (orders, orders[product] = "B" || orders[product] = "C" && orders[Cohort Date] == SELECTEDVALUE('Order Date'[Yr & mt])))
I want to add a new condition in VAR _custA, which is orders[date] < DATE(2021,12,1), because the launch of product B and C was in december.
I tried this but it doesnt show any values after the modification.
Measure =
VAR _custA =
SUMMARIZE (
FILTER (orders,orders[product]= "A" && orders[date].[DATE]<DATE(2021,12,1)),
orders[customer ID],
"Sales", SUM (orders[sales]))
RETURN
CALCULATE(
DISTINCTCOUNT( orders[customer ID] ),
FILTER ( _custA, [customer ID] = orders[customer ID]),FILTER (orders, orders[product] = "B" || orders[product] = "C" && orders[Cohort Date] == SELECTEDVALUE('Order Date'[Yr & mt])))
Any idea on what the issue might be and how to fix it?
Solved! Go to Solution.
@Anonymous , try like
measure 1 = calculate(SUM (orders[sales]),FILTER (orders,orders[product]= "A" && orders[date]<DATE(2021,12,1)) )
or better use date table, you might need all to ignore selected filter
measure 1 = calculate(SUM (orders[sales]),FILTER (orders,orders[product]= "A"),FILTER (all(date), date[date]<DATE(2021,12,1))
Assuming date table is connected order date and this measure need selected date
Measure 2 = calculate(SUM (orders[sales]),FILTER (orders,orders[product] in {"B", "C"}) )
final meausre =
countx(values(orders[customer ID]), if ( not(isblank([measure 1]) ) && not(isblank([measure 2]) ) , [customer ID], blank())
same approch but for different product
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458
@Anonymous , try like
measure 1 = calculate(SUM (orders[sales]),FILTER (orders,orders[product]= "A" && orders[date]<DATE(2021,12,1)) )
or better use date table, you might need all to ignore selected filter
measure 1 = calculate(SUM (orders[sales]),FILTER (orders,orders[product]= "A"),FILTER (all(date), date[date]<DATE(2021,12,1))
Assuming date table is connected order date and this measure need selected date
Measure 2 = calculate(SUM (orders[sales]),FILTER (orders,orders[product] in {"B", "C"}) )
final meausre =
countx(values(orders[customer ID]), if ( not(isblank([measure 1]) ) && not(isblank([measure 2]) ) , [customer ID], blank())
same approch but for different product
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458
Perfect, thank you!
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 |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |