DAX : Count rows before specific row

[Anonymous]

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

[tex628 · Accepted solution]

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 )

[Anonymous]

Nice it works 🙂

Screenshot tested for another parcel :

Thanks a lot !

Driss

[tex628]

No worries! Let me know if there are any other issues.

/ J

[tex628]

Is there only 1 row that has the combination of ObjectEvent & Reader?

/ J

[Anonymous]

Hello, 

For each parcel yes there is only one combinaison ObjectEvent & Reader

The screen shows the case for only one parcel