Count on first occurrence grouping of qualifying results by location

[adamnimmo]

Morning All, hoping I could get a bit of assistance with a formula I have been stuck on.

I am trying to count the number of instances based on qualifying audit results by location and the latest cluster of instances.

ResultDate	Phase	AuditScoreSort	AuditScoreSetInOrder	AuditScoreShine	AuditScoreSafety	ResultDateSoW	Sort	Location	Index	Qualifying	QualfyingCount	QualifyingResultNeeded
4/22/2020	Set In Order	4	4		4	Wednesday, April 22, 2020	2	Cell1	669	1	9	7
4/9/2020	Set In Order	3.5	3.8		4	Thursday, April 9, 2020	2	Cell1	670	1	9	7
3/26/2020	Set In Order	4	3.4		4	Thursday, March 26, 2020	2	Cell1	671	1	9	7
3/9/2020	Set In Order	4	3.4		4	Monday, March 9, 2020	2	Cell1	672	1	9	7
2/29/2020	Set In Order	3.6	3.4		4	Saturday, February 29, 2020	2	Cell1	673	1	9	7
2/28/2020	Set In Order	3.1	3.1		4	Friday, February 28, 2020	2	Cell1	674	1	9	7
2/10/2020	Set In Order	3.1	3.7		4	Monday, February 10, 2020	2	Cell1	675	1	9	7
1/31/2020	Set In Order	4	2.8		4	Friday, January 31, 2020	2	Cell1	676		9	7
1/15/2020	Set In Order	3.6	2.8		4	Wednesday, January 15, 2020	2	Cell1	677		9	7
12/31/2019	Set In Order	3.1	2.8		3.571	Tuesday, December 31, 2019	2	Cell1	678		9	7
12/17/2019	Set In Order	3.6	3.1		3.8	Tuesday, December 17, 2019	2	Cell1	679	1	9	7
12/5/2019	Set In Order	4	3.4		4	Thursday, December 5, 2019	2	Cell1	680	1	9	7
4/23/2020	Sort	3.5			3.6	Thursday, April 23, 2020	1	Cell2	715	1	5	3
4/23/2020	Sort	3.1			3.8	Thursday, April 23, 2020	1	Cell2	716	1	5	3
4/22/2020	Sort	3.5			3.6	Wednesday, April 22, 2020	1	Cell2	717	1	5	3
4/8/2020	Sort	2.7			3.2	Wednesday, April 8, 2020	1	Cell2	718		5	3
3/26/2020	Sort	2.3			2.93	Thursday, March 26, 2020	1	Cell2	719		5	3
3/11/2020	Sort	3.1			3.4	Wednesday, March 11, 2020	1	Cell2	720	1	5	3
2/24/2020	Sort	3.6			4	Monday, February 24, 2020	1	Cell2	721	1	5	3

[Syndicate_Admin]

Hi @adamnimmo ,

Create two columns like this:

Column 1 = 
VAR thislocation = Sheet1[Location]
VAR FirstLessThan3Index =
    CALCULATE (
        MIN ( Sheet1[Index] ),
        FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Location] ), [Qualifying] = BLANK () )
    )
RETURN
    IF (
        FirstLessThan3Index > Sheet1[Index]
            && Sheet1[Location] = thislocation,
        1,
        0
    )

Column 2 = 
VAR thislocation = Sheet1[Location]
RETURN
    SUMX ( FILTER ( Sheet1, Sheet1[Location] = thislocation ), [Column 1] )

BTW, attached .pbix file.

Best regards

Icey

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

[Icey · Accepted solution]

Hi @adamnimmo ,

Create two columns like so:

Column 1 = 
VAR thislocation = Sheet1[Location]
VAR FirstLessThan3Index =
    CALCULATE (
        MIN ( Sheet1[Index] ),
        FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Location] ), [Qualifying] = BLANK () )
    )
RETURN
    IF (
        FirstLessThan3Index > Sheet1[Index]
            && Sheet1[Location] = thislocation,
        1,
        0
    )

Column 2 = 
VAR thislocation = Sheet1[Location]
RETURN
    SUMX ( FILTER ( Sheet1, Sheet1[Location] = thislocation ), [Column 1] )

BTW, .pbix file attached.

Best Regards,

Icey

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

[adamnimmo]

@Icey You are brilliant, thank you so much this works perfect.

[parry2k]

@adamnimmo data is very hard to understand with all the column connected, can you paste the data in table format or share thru excel and also post the required result.

[adamnimmo]

Sure thing @parry2k  here is the data in an excel table.

Example.xlsx 

[parry2k]

@adamnimmo thanks for the excel file, but whst is the logic to achieve the result?

[adamnimmo]

@parry2k  I have two example locations (Cell1 & Cell2) for Cell1 we have 9 qualifying audits (all scores above 3.0) however we also have a gap in concurrent qualyfing audits (Row 9-11). So within this cell example I want to only count the latest group of qualifying audits (7x) since this is the latest concurrent results. The same logic is true for the Cell2 example, I need to only return the latest grouping of concurrent results (3x).