Identifying relevant rows

[Daretoexplore]

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!

[Greg_Deckler · Accepted 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]

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.

[Greg_Deckler · Accepted solution]

@Daretoexplore Likely need to use >= in the column formula when comparing to __MaxDate instead of =. 

[Daretoexplore]

Thank you. Kudos given

[Greg_Deckler · Accepted 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