Forum Discussion
Helper ICount Number test[Column(Failures)] = 1 when column X last row = 0.2
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?
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])
5 Replies
- Greg_Deckler
Community Champion
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])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_DecklerNot sure, seems to work for me. See Page 10 and Table 15 in attached file.
