Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all
My dataset records everything, and I am struggling to create something that helps sort it.
My dataset is as follows:
Cust ID. Status. Date (UK)
CST001 Entered shop 1/2/2024
CST001 Enter Aisle 1 1/2/2024
CST001 Enter Aisle 6 1/2/2024
CST001 Checkouts 1/2/2024
CST001 Exited shop 1/2/2024
CST001 Entered shop 5/2/2024
CST001 Exited shop 5/2/2024
CST001 Entered shop 8/2/2024
CST001 Enter Aisle 1 8/2/2024
CST001 Enter Aisle 6 8/2/2024
CST001 Exited shop 8/2/2024
What I'm trying to solve are 2 problem as follows:
1) With 'Entered Shop' being the start of the journey and 'Exited shop' being the end, is there a way I can use DAX to write a column which assigns a 1 to all rows pertaining to the most recent visit, and a 0 to all other visits.
2) Can someone assist with a measure that works out how many times that customer has visited the shop before the current one, so in this example, the answer would be 2.
Many thanks!
Solved! Go to Solution.
@Daretoexplore Might be best to have an index column, but you could do it like this:
Column =
VAR __Cust = [Cust ID.]
VAR __MaxDate = MAXX( FILTER( 'Table', [Cust ID.] = __Cust && [Status.] = "Entered Shop" ), [Date (UK)])
VAR __Result = IF( [Date (UK)] = __MaxDate, 1, 0 )
RETURN
__Result
Then for the measure:
Measure =
VAR __Cust = MAX('Table'[Cust ID.])
VAR __Table = SUMMARIZE( FILTER('Table', [Column] = 0), [Date (UK)] )
VAR __Result = COUNTROWS( __Table )
RETURN
__Result
@Daretoexplore Likely need to use >= in the column formula when comparing to __MaxDate instead of =.
Actually @Greg_Deckler
Whilst I think about it. It is a 24 hour shop, so if in that example the Entered Shop was on the 7/2/2024 instead of the 8th.l, however the 'Exited Shop' was still on the 8/2/2024. How would the DAX need to be adapted for that.
@Daretoexplore Likely need to use >= in the column formula when comparing to __MaxDate instead of =.
Thank you. Kudos given
@Daretoexplore Might be best to have an index column, but you could do it like this:
Column =
VAR __Cust = [Cust ID.]
VAR __MaxDate = MAXX( FILTER( 'Table', [Cust ID.] = __Cust && [Status.] = "Entered Shop" ), [Date (UK)])
VAR __Result = IF( [Date (UK)] = __MaxDate, 1, 0 )
RETURN
__Result
Then for the measure:
Measure =
VAR __Cust = MAX('Table'[Cust ID.])
VAR __Table = SUMMARIZE( FILTER('Table', [Column] = 0), [Date (UK)] )
VAR __Result = COUNTROWS( __Table )
RETURN
__Result
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
151 | |
121 | |
111 | |
107 | |
95 |