IF(CountIFS Greater than + other criteria

[moeconsult]

Please see below my excel formula which is returning the right result 

Excel Formula =IF(COUNTIFS(A:A,A2,B:B,"cs-ac",C:C,"Passed")>0,"Passed","No")

I have tried the dax formulas below but not returning what I expect, any help to perfect Dax would be great .

Status = IF(COUNT(Data[value])>0,IF(AND(Data[type]="cs-ac",Data[state]="Passed"),"Passed","No"))

Status = Var P = COUNT(Data[value])

Return

SWITCH(

TRUE(),

P >0,IF(AND([sic_type]="cs-ac",[state]="Passed"),"Passed","No"))

Sample data and output

A               B                   C                          D

value	Type	State	Output
01ed570e	under	complete	No
01ed570e	progr	complete	No
01ed570e	under	complete	No
01ed570e	data-	complete	No
01ed570e	an-in	complete	No
01ed570e	regis	complete	No
01ed570e	netwo	complete	No
01ed570e	cs-ac	complete	No
01ed570e	algor	complete	No
01ed570e	cs-ac	complete	No
01ed570e	repre	in_progress	No
0423565b	progr	complete	Passed
0423565b	under	complete	Passed
0423565b	regis	complete	Passed
0423565b	netwo	complete	Passed
0423565b	cs-ac	complete	Passed
0423565b	pytho	complete	Passed
0423565b	algor	complete	Passed
0423565b	cs-ac	complete	Passed
0423565b	progr	enrolled	Passed
0423565b	netwo	enrolled	Passed
0423565b	progr	enrolled	Passed
0423565b	repre	enrolled	Passed
0423565b	teach	enrolled	Passed
0423565b	how-c	enrolled	Passed
0423565b	intro	enrolled	Passed
0423565b	intro	enrolled	Passed
0423565b	desig	enrolled	Passed
0423565b	objec	enrolled	Passed
0423565b	under	enrolled	Passed
0423565b	data-	dropped	Passed
0423565b	progr	in_progress	Passed
0423565b	intro	in_progress	Passed
0423565b	ncce-	dropped	Passed
0423565b	gcse-	dropped	Passed
0423565b	impac	in_progress	Passed
0423565b	cs-ac	passed	Passed

@Greg_Deckler @Ashish_Mathur 

PM

[nandukrishnavs]

@moeconsult 

I couldn't understand the logic for the expected output. Could you explain it?

Please refer to the below logic for the calculated column.

Status = 
var _Count=COUNTX (FILTER ( 'Table', EARLIER ( 'Table'[value] ) = 'Table'[value]  ),'Table'[value] )
var _type= IF('Table'[Type]="cs-ac",TRUE(),FALSE())
var _state= IF('Table'[state]="Passed",TRUE(),FALSE())
var _status= IF(_Count>0,IF(AND(_type,_state),"Passed","No"),"Passed")
return _status

Maybe you have to tweak the logic of the _status variable.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[moeconsult]

It working perfectly okay for No but not its returning Pass and No for when I expect only Pass

[AlB]

@moeconsult 

I don't know who you're answering to. If you are referring to my solution, it yields exactly the output that you show in your table

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[moeconsult]

I am replying to both solutions thanks

[v-xuding-msft]

Hi @moeconsult ,

 I try the formula that @AlB suggested and it works fine in my sample.  So I'm confused what you encountered. Can you please share a few screenshots of what you tried?  More details will help us understand more clearly.

[moeconsult]

Status =
var _Count=COUNTX (FILTER ( 'Data', EARLIER ('Data'[Value]) = Data[Value]),Data[Value])

var _type= IF(Data[type]="cs-ac",TRUE(),FALSE())

var _state= IF(Data[state]="Passed",TRUE(),FALSE())

var _status= IF(_Count>0,IF(AND(_type,_state),"Passed","No"),"Passed")
return _status

That is the formula that I tried 

Please see screenshot above is meant to be Pass through out but its returning NO . 

[v-xuding-msft]

Hi @moeconsult ,

I reproduced it using your formula. Please try the formula that @AlB suggested. I tried this and it worked fine.

Status = 
IF (
    CALCULATE (
        COUNT ( Data[Value] ),
        Data[Type] = "cs-ac",
        Data[State] = "Passed"
    ) > 0,
    "Passed",
    "No"
)

If this still can't work for you, please share more details.

[moeconsult]

@v-xuding-msft @AlB 

I am still having same issues, please see screenshot with my actuall data for both "Passed" and "No" 

As before its working well for the No but return No and Pass for when it should be pass alone .

Works FineStill returning No when it should all be Pass

@v-xuding-msft @AlB  Any suggestion would help.

[moeconsult · Accepted solution]

I have managed to fix the problem by creating a calculated Table, please formula below :

Status= SUMMARIZE('Table',[value],"Achievement",IF(CALCULATE(COUNT('Table',[value]),'Table',[Type]= "cs-ac",'Table',[state]="pass")>=1,"Passed","No"))

[AlB]

Hi @moeconsult 

For the value to get an outpu "Passed" there must be at least one row in which  Type is "cs-ac" and State "Passed". Is this correct? If so, you can create a calculated column in your table:

Output =
IF (
    CALCULATE (
        COUNT ( Table1[Value] ),
        Table1[Type] = "cs-ac",
        Table1[State] = "Passed"
    ) > 0,
    "Passed",
    "No"
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[moeconsult]

Thanks for your help, this working but not perfect, its returning No for everything that meant to be no but it is returning Pass and No for all that is meant to be Only Pass