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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BlastS
Helper I
Helper I

Count 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?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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])

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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])

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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 ? 

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

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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)

@BlastS - Awesome! Glad you got it!


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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