Count Number test[Column(Failures)] = 1 when column X last row = 0.2

[BlastS]

I am new to dax formulas i already tryend some things but nothing is realy working to get this result, actualy i got this columns for use to this calculation:

Index;Failures; ColumnX; 

1           0              0

2           0              0.2

3           1               0

4           0               0

5           1               0

6           0              0.2

7           1               0

...

I want to count all failures = 1 when columnX last row = 0.2. Which means when its one it needs to check if last row of column X is 0.2 to count otherwise dont count.

This is my try so far : 

Count of Failures =CALCULATE(COUNTAX(TableA,TableA[Failures]) = 1, LOOKUPVALUE(TableA[ColumnX)],TableA[Index],TableA[Index]-1),TableA[ColumnX])

ERROR RETURNED:

A LOOKUPVALUE function was used in a True / False expression used as a table filter expression. This is not allowed.

OUTPUT should BE:

The output of count number of failures = 1 should be: 2

How can i achieve this in powerbI?

[Greg_Deckler · Accepted solution]

Try something like:

Count of Failures = 
VAR __table = ADDCOLUMNS('Table',"__failure",IF([Failures]=1 && MAXX(FILTER('Table',[Index]=EARLIER([Index])-1),[ColumnX])=.2,1,0)) 
RETURN SUMX(__table,[__failure])

[Anonymous]

Count ASD =
VAR __table = ADDCOLUMNS(Geradores,"__failure",IF([Failure Event]=1 && MAXX(FILTER('Geradores',[Index]=EARLIER([Index])-1),[columnX)])=.2,1,0))
RETURN SUMX(__table,[__failure])

i tryed this and its returning some insane values i cant understand dwhy, it should return only 7 instead of 135.

Count all 1 failures events that has columnx index-1 = 0.2  what could possibly be wrong ? 

[Greg_Deckler]

Not sure, seems to work for me. See Page 10 and Table 15 in attached file.

[BlastS]

I manipulate a litle bit your solution to my case and it worked, thanks buddy.

My problem was the index, since i got for example 8 datasets the index was the same for each dataset so i create a new one for all and it work fine.

Solution: 

FailuresCount = IF([Failure])=1 && MAXX(FILTER('Table',[Index]=EARLIER([Index])-1),[ColumnX)])=.2,1,0)

[Greg_Deckler]

@BlastS - Awesome! Glad you got it!