How to get pass/fail for any kpi for successive failed site for more than 2 Quarters

[Anonymous]

Hi Team

Below is the sample data- 

Country	Site	KPIs	Month	Value	PassFail(1,0)	Quarter
India	Chennai (Akash)	KPI2	Feb-21	Fail	1	Q1
India	Chennai (Akash)	KPI5	Feb-21	Pass	0	Q1
India	Chennai (Akash)	KPI6	Feb-21	Fail	1	Q1
India	Chennai (Akash)	KPI7	Feb-21	Pass	0	Q1
India	Chennai (Akash)	KPI2	May-21	Pass	0	Q2
India	Chennai (Akash)	KPI5	May-21	Pass	0	Q2
India	Chennai (Akash)	KPI6	May-21	Pass	0	Q2
India	Chennai (Akash)	KPI7	May-21	Pass	0	Q2
India	Chennai (Akash)	KPI5	Aug-21	Pass	0	Q3
India	Chennai (Akash)	KPI6	Aug-21	Fail	1	Q3
India	Chennai (Akash)	KPI7	Aug-21	Pass	0	Q3
India	Chennai (Akash)	KPI5	Nov-21	Pass	0	Q4
India	Chennai (Akash)	KPI6	Nov-21	Pass	0	Q4
India	Chennai (Akash)	KPI7	Nov-21	Pass	0	Q4
India	Chennai (Akash)	KPI8	Feb-21	Fail	1	Q1
India	Chennai (Akash)	KPI8	May-21	Fail	1	Q2
India	Chennai (Akash)	KPI8	Aug-21	Fail	1	Q3
India	Chennai (Akash)	KPI8	Nov-21	Fail	1	Q4
Vietnam	Hanoi	KPI2	Feb-21	Fail	1	Q1
Vietnam	Hanoi	KPI2	May-21	Fail	1	Q2
Vietnam	Hanoi	KPI2	Aug-21	Fail	1	Q3
Vietnam	Hanoi	KPI5	Feb-21	Pass	0	Q1
Vietnam	Hanoi	KPI5	May-21	Pass	0	Q2
Vietnam	Hanoi	KPI5	Aug-21	Pass	0	Q3
Vietnam	Hanoi	KPI5	Nov-21	Pass	0	Q4
Vietnam	Hanoi	KPI6	Feb-21	Fail	1	Q1
Vietnam	Hanoi	KPI6	May-21	Pass	0	Q2
Vietnam	Hanoi	KPI6	Aug-21	Fail	1	Q3
Vietnam	Hanoi	KPI6	Nov-21	Pass	0	Q4
Vietnam	Hanoi	KPI7	Feb-21	Pass	0	Q1
Vietnam	Hanoi	KPI7	May-21	Fail	1	Q2
Vietnam	Hanoi	KPI7	Aug-21	Pass	0	Q3
Vietnam	Hanoi	KPI7	Nov-21	Pass	0	Q4
Vietnam	Hanoi	KPI8	Feb-21	Fail	1	Q1
Vietnam	Hanoi	KPI8	May-21	Fail	1	Q2
Vietnam	Hanoi	KPI8	Aug-21	Fail	1	Q3
Vietnam	Hanoi	KPI8	Nov-21	Fail	1	Q4
Thailand	Bangkok	KPI2	Feb-21	Pass	0	Q1
Thailand	Bangkok	KPI5	Feb-21	Pass	0	Q1
Thailand	Bangkok	KPI5	May-21	Pass	0	Q2
Thailand	Bangkok	KPI5	Aug-21	Pass	0	Q3
Thailand	Bangkok	KPI5	Nov-21	Pass	0	Q4
Thailand	Bangkok	KPI6	Feb-21	Fail	1	Q1
Thailand	Bangkok	KPI6	May-21	Pass	0	Q2
Thailand	Bangkok	KPI6	Aug-21	Fail	1	Q3
Thailand	Bangkok	KPI6	Nov-21	Pass	0	Q4
Thailand	Bangkok	KPI7	Feb-21	Pass	0	Q1
Thailand	Bangkok	KPI7	May-21	Pass	0	Q2
Thailand	Bangkok	KPI7	Aug-21	Pass	0	Q3
Thailand	Bangkok	KPI7	Nov-21	Pass	0	Q4
Thailand	Bangkok	KPI8	Feb-21	Fail	1	Q1
Thailand	Bangkok	KPI8	May-21	Pass	0	Q2
Thailand	Bangkok	KPI8	Aug-21	Fail	1	Q3
Thailand	Bangkok	KPI8	Nov-21	Fail	1	Q4
Singapore	Singapore (Mapletree)	KPI2	Feb-21	Fail	1	Q1
Singapore	Singapore (Mapletree)	KPI5	Feb-21	Pass	0	Q1
Singapore	Singapore (Mapletree)	KPI5	May-21	Pass	0	Q2
Singapore	Singapore (Mapletree)	KPI5	Aug-21	Pass	0	Q3
Singapore	Singapore (Mapletree)	KPI5	Nov-21	Pass	0	Q4
Singapore	Singapore (Mapletree)	KPI6	Feb-21	Fail	1	Q1
Singapore	Singapore (Mapletree)	KPI6	May-21	Pass	0	Q2
Singapore	Singapore (Mapletree)	KPI6	Aug-21	Fail	1	Q3
Singapore	Singapore (Mapletree)	KPI6	Nov-21	Pass	0	Q4
Singapore	Singapore (Mapletree)	KPI7	Feb-21	Pass	0	Q1
Singapore	Singapore (Mapletree)	KPI7	May-21	Pass	0	Q2
Singapore	Singapore (Mapletree)	KPI7	Aug-21	Pass	0	Q3
Singapore	Singapore (Mapletree)	KPI7	Nov-21	Pass	0	Q4
Singapore	Singapore (Mapletree)	KPI8	Feb-21	Fail	1	Q1
Singapore	Singapore (Mapletree)	KPI8	May-21	Fail	1	Q2
Singapore	Singapore (Mapletree)	KPI8	Aug-21	Fail	1	Q3
Singapore	Singapore (Mapletree)	KPI8	Nov-21	Fail	1	Q4

and below is the requirement for result

KPIs	Q1	Q2	Q3	Q4
KPI2
KPI5
KPI6
KPI7
KPI8

I want to decide pass/fail for available kpis on the basis whether a site is failed for two successive quarter -> Is there any way to apply this logic?

Please help.. Please please please... I am indire strait of this... please look into this.

Thanks and regards

RatanB 

@amitchandak Please look and help sir

[Anonymous · Accepted solution]

var _successive_Fail=CALCULATE(

COUNT(SummaryKPI[Quarter Concatenate]),

FILTER(

ALLSELECTED(SummaryKPI[Quarter]),

ISONORAFTER(SummaryKPI[Quarter],MAX(SummaryKPI[Quarter]),desc)

))

Return

Above dax helped/led me in solving my above queries. Thanks to all the mentors to look into this.

[VahidDM]

Hi @Anonymous 

Add more details about the result? do you want to show the count of fails in each Quarter for each KPIs like this:

Or just want to show the count of the failed for two successive quarters in that table?

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos ✌️!!

[Anonymous]

Thanks for your reply.. This is wat I have achieved.. what I am trying here to check

1) whether a site is failed in successive quarter - like if chennai has failed in Q1 and then again in Q1 for kpi2.. then it should show result as fail. And same for other KPIs...

[VahidDM]

@Anonymous 

Can you create a sample of your result Table/Column/... in excel and share it here?

[Anonymous]

Hey morning Vahid

Below is the sample of table

KPIs	Q1	Q2	Q3	Q4
KPI2	Pass	Fail	Fail	Pass
KPI5
KPI6
KPI7
KPI8

Only condition is if any site is found failed in successive quarter - after that quarter - must be failed.

Thanks and regards

RatanB