Forum Discussion
Identifying relevant rows
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!
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 __ResultThen for the measure:
Measure = VAR __Cust = MAX('Table'[Cust ID.]) VAR __Table = SUMMARIZE( FILTER('Table', [Column] = 0), [Date (UK)] ) VAR __Result = COUNTROWS( __Table ) RETURN __ResultDaretoexplore Likely need to use >= in the column formula when comparing to __MaxDate instead of =.
4 Replies
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 __ResultThen for the measure:
Measure = VAR __Cust = MAX('Table'[Cust ID.]) VAR __Table = SUMMARIZE( FILTER('Table', [Column] = 0), [Date (UK)] ) VAR __Result = COUNTROWS( __Table ) RETURN __ResultThank you. Kudos given
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 =.
