Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Daretoexplore
Frequent Visitor

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!

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Daretoexplore
Frequent Visitor

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 =. 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Daretoexplore
Frequent Visitor

Thank you. Kudos given

Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.