Forum Discussion
DAX : Count rows before specific row
Hello,
I'm struggling with a DAX (or logic) calculation, I have a 'Qualification' parcel Table which contains these columns :
- Event_Biztranslist : ID of parcel
- Event_Type : Type of event
- Event_Time : Date/Time of the event
- Readpoint_Type : "Tunnel" or "Reader"
- Rank : for each parcel, ranking events by Event_Time (first = most ancient, last = most recent)
I want to count only rows before a specific row :
[Event_Type] = "ObjectEvent" && [Readpoint_Type] "Reader" --> the combinaison occurs only once by parcels
First I want to qualify these rows by creating a column which will contains 1 if the row is before the row above, 0 else.
To better understand :
The screen above concerns only one parcel, I want to do it for all parcels
The result in the column [Z. Check Tra before Obj] should be :
1
1
1
0
0
0
Thanks in advance for your tips
Try this,
Column = VAR Parcel = 'Table'[Event_Biztransit] VAR EventReader = Calculate( MAX( 'Table'[Event_Time] ) , ALL('Table') , 'Table'[Event_Biztransit] = Parcel , Table'[Event_Type] = "ObjectEvent" , Table'[Readpoint_Type] = "Reader") Return IF('Table'[Event_Time]<EventReader , 1 , 0 )
5 Replies
Is there only 1 row that has the combination of ObjectEvent & Reader?
/ JTry this,
Column = VAR Parcel = 'Table'[Event_Biztransit] VAR EventReader = Calculate( MAX( 'Table'[Event_Time] ) , ALL('Table') , 'Table'[Event_Biztransit] = Parcel , Table'[Event_Type] = "ObjectEvent" , Table'[Readpoint_Type] = "Reader") Return IF('Table'[Event_Time]<EventReader , 1 , 0 )No worries! Let me know if there are any other issues.
/ J