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.
Dear Community,
I have a model with an Accounts, Orders, Order Details, Product Details and Calendar table, with all relationships in place. Please download anonymous pbix file HERE.
I have a challenge creating measures which computes the following from the Order Table:
Solved! Go to Solution.
@ChumaAmako - I would try this way:
No of Active Users =
VAR __MaxDate = MAXX(ALL('Order Table'),[Order Start Date])
VAR __MinDate = __MaxDate - 28
VAR __Table = FILTER(ALL('Order Table'),[Order Start Date]>=__MinDate)
RETURN
COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Email",[User Email])))
Hi @ChumaAmako ,
Create 2 measures as below:
_Count active = CALCULATE(COUNT('Accounts Table'[Email Address]),FILTER(ALL('Accounts Table'),'Accounts Table'[Email Address]=MAX('Accounts Table'[Email Address])&&'Accounts Table'[Days Since Last Purchase]<=28))
_No of Active Users =
CALCULATE(DISTINCTCOUNT('Accounts Table'[Email Address]),FILTER('Accounts Table','Accounts Table'[_Count active]>=1)
)
And you will see:
For the related .pbix file,pls see attached.
Hi @ChumaAmako ,
Create 2 measures as below:
_Count active = CALCULATE(COUNT('Accounts Table'[Email Address]),FILTER(ALL('Accounts Table'),'Accounts Table'[Email Address]=MAX('Accounts Table'[Email Address])&&'Accounts Table'[Days Since Last Purchase]<=28))
_No of Active Users =
CALCULATE(DISTINCTCOUNT('Accounts Table'[Email Address]),FILTER('Accounts Table','Accounts Table'[_Count active]>=1)
)
And you will see:
For the related .pbix file,pls see attached.
@ChumaAmako - I would try this way:
No of Active Users =
VAR __MaxDate = MAXX(ALL('Order Table'),[Order Start Date])
VAR __MinDate = __MaxDate - 28
VAR __Table = FILTER(ALL('Order Table'),[Order Start Date]>=__MinDate)
RETURN
COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Email",[User Email])))
Hi @Greg_Deckler thanks a lot for the solution.
Any idea on how I can approach the second measure
The number of Passive Consumers; This count of the unique consumers who have not purchased in the past 28 days, but has purchased in the last 35 days and has at least 1 previous order.
@ChumaAmako - Here is a variation on the theme for the passive uers.
No of Passive Users =
VAR __MaxDate = MAXX(ALL('Order Table'),[Order Start Date])
VAR __MinDate = __MaxDate - 28
VAR __MinDate2 = __MaxDate - 35
VAR __Table1 = SELECTCOLUMNS(FILTER(ALL('Order Table'),[Order Start Date]<__MinDate1),"Email",[User Email]) // users who have bought > 35 days ago.
VAR __Table2 = SELECTCOLUMNS(FILTER(ALL('Order Table'),[Order Start Date]>=__MinDate1 && [Order Start Date]<__MinDate),"Email",[User Email]) //users bought between 28-35 days agao
VAR __Table3 = SELECTCOLUMNS(FILTER(ALL('Order Table'),[Order Start Date]>=__MinDate && [Order Start Date]<=__MaxDate),"Email",[User Email]) // users bought in last 28 days
VAR __Table = INTERSECT(EXCEPT(__Table2,Table3),__Table1)
RETURN
COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Email",[User Email])))
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |