Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@adamnimmo thanks for the excel file, but whst is the logic to achieve the result?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |