Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

HELP, Dax formula for Current Streak

‎03-16-2020 12:42 PM

I am workign through a formula that is intended to show the current streak for a given hospital that is below 75% in theor satisfaction scores.  I have an accurate formula for the LONGEST streak but cannot figure out how to make ti the current streak??

 

Dat a is 1 row per hospital and fiscal period in columns oldest to newest.

 

Consecutive Period Under 75% =
SWITCH (
TRUE (),
'OSAT Streak (2)'[Under 75%] = 1, 0,
CALCULATE (
COUNTROWS ( 'OSAT Streak (2)' ),
FILTER (
ALL ( 'OSAT Streak (2)' ),
'OSAT Streak (2)'[Under 75%] = 1
&& 'OSAT Streak (2)'[Hosp Num] = EARLIER ( 'OSAT Streak (2)'[Hosp Num] )
&& 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] >EARLIER ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )
)
)
= 0, CALCULATE (
SUM ( 'OSAT Streak (2)'[Under 75%] ),
FILTER (
ALL ( 'OSAT Streak (2)' ),
'OSAT Streak (2)'[Hosp Num] = EARLIER ( 'OSAT Streak (2)'[Hosp Num] )
&& 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] >= EARLIER ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )
)
),
CALCULATE (
SUM ( 'OSAT Streak (2)'[Under 75%] ),
FILTER (
ALL ( 'OSAT Streak (2)' ),
'OSAT Streak (2)'[Hosp Num] = EARLIER ( 'OSAT Streak (2)'[Hosp Num] )
&& 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]
> CALCULATE (
MAX ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] ),
FILTER (
ALL ( 'OSAT Streak (2)' ),
'OSAT Streak (2)'[Under 75%] = 0
&& 'OSAT Streak (2)'[Hosp Num] = EARLIEST ( 'OSAT Streak (2)'[Hosp Num] )
&& 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] < EARLIEST ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )
)
)
&& 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] <= EARLIER ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )
)
)
)

 

Labels:
Message 1 of 7
1,660 Views
0
1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support
In response to Anonymous

‎03-17-2020 07:01 PM

Hi @Anonymous ,

 

Thank you for your additional information, we can try to create a measure to meet your requirement:

 

Consecutive Measure = 
VAR LastUnderDate =
    CALCULATE (
        MAX ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] ),
        FILTER (
            'OSAT Streak (2)',
            'OSAT Streak (2)'[Under 75%] = 0
                && 'OSAT Streak (2)'[Hosp Num] in DISTINCT('OSAT Streak (2)'[Hosp Num])
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]
                    <= MAX('OSAT Streak (2)'[Mars Fiscal Mth End Dt])
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'OSAT Streak (2)' ),
        FILTER (
            'OSAT Streak (2)',
            'OSAT Streak (2)'[Under 75%] = 1
                && 'OSAT Streak (2)'[Hosp Num]  in DISTINCT('OSAT Streak (2)'[Hosp Num])
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]
                    <= MAX('OSAT Streak (2)'[Mars Fiscal Mth End Dt])
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] >= LastUnderDate
        )
    )+0

 

3.jpg


Best regards,

 

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

HELP-Dax-formula-for-Current-Streak.pbix
Message 5 of 7
1,597 Views
0
6 REPLIES 6
andre
Memorable Member
Memorable Member

‎02-21-2024 01:25 PM

here is a video on how to create a measure to do Streak analysis without doing anything with calculated columns:

Message 7 of 7
976 Views
0
Anonymous
Not applicable

‎03-17-2020 08:44 AM

Sample Data set below, using these two examples I would want to see a 0 for #47 and a 3 for #59

Mars Fiscal Yr and PdHosp NumOverall Satisfaction - All ClientsMars Fiscal Mth End Dt
2017064778.95%6/17/2017
2017074780.85%7/15/2017
2017084776.47%8/12/2017
2017094780.36%9/9/2017
2017104775.00%10/7/2017
2017114774.63%11/4/2017
2017124769.33%12/2/2017
2017134766.67%12/30/2017
2018014770.67%1/27/2018
2018024768.75%2/24/2018
2018034777.33%3/24/2018
2018044775.71%4/21/2018
2018054791.04%5/19/2018
2018064788.89%6/16/2018
2018074792.31%7/14/2018
2018084786.90%8/11/2018
2018094786.42%9/8/2018
2018104780.72%10/6/2018
2018114777.01%11/3/2018
2018124776.25%12/1/2018
2018134777.33%12/29/2018
2019014780.00%1/26/2019
2019024775.93%2/23/2019
2019034776.52%3/23/2019
2019044773.88%4/20/2019
2019054776.72%5/18/2019
2019064782.05%6/15/2019
2019074783.81%7/13/2019
2019084781.48%8/10/2019
2019094781.65%9/7/2019
2019104789.19%10/5/2019
2019114789.19%11/2/2019
2019124791.26%11/30/2019
2019134786.32%12/28/2019
2020014787.23%1/25/2020
2020024781.61%2/22/2020
2017065968.75%6/17/2017
2017075970.59%7/15/2017
2017085969.05%8/12/2017
2017095975.00%9/9/2017
2017105968.60%10/7/2017
2017115967.68%11/4/2017
2017125961.80%12/2/2017
2017135972.53%12/30/2017
2018015979.07%1/27/2018
2018025981.55%2/24/2018
2018035978.35%3/24/2018
2018045972.22%4/21/2018
2018055966.67%5/19/2018
2018065956.79%6/16/2018
2018075968.54%7/14/2018
2018085967.59%8/11/2018
2018095971.68%9/8/2018
2018105967.86%10/6/2018
2018115966.67%11/3/2018
2018125970.53%12/1/2018
2018135968.60%12/29/2018
2019015969.09%1/26/2019
2019025968.38%2/23/2019
2019035971.69%3/23/2019
2019045976.32%4/20/2019
2019055981.05%5/18/2019
2019065983.33%6/15/2019
2019075978.63%7/13/2019
2019085972.58%8/10/2019
2019095969.13%9/7/2019
2019105974.03%10/5/2019
2019115976.76%11/2/2019
2019125978.70%11/30/2019
2019135970.65%12/28/2019
2020015968.13%1/25/2020
2020025970.09%2/22/2020

 

Message 4 of 7
1,609 Views
0
v-lid-msft
Community Support
Community Support
In response to Anonymous

‎03-17-2020 07:01 PM

Hi @Anonymous ,

 

Thank you for your additional information, we can try to create a measure to meet your requirement:

 

Consecutive Measure = 
VAR LastUnderDate =
    CALCULATE (
        MAX ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] ),
        FILTER (
            'OSAT Streak (2)',
            'OSAT Streak (2)'[Under 75%] = 0
                && 'OSAT Streak (2)'[Hosp Num] in DISTINCT('OSAT Streak (2)'[Hosp Num])
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]
                    <= MAX('OSAT Streak (2)'[Mars Fiscal Mth End Dt])
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'OSAT Streak (2)' ),
        FILTER (
            'OSAT Streak (2)',
            'OSAT Streak (2)'[Under 75%] = 1
                && 'OSAT Streak (2)'[Hosp Num]  in DISTINCT('OSAT Streak (2)'[Hosp Num])
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]
                    <= MAX('OSAT Streak (2)'[Mars Fiscal Mth End Dt])
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] >= LastUnderDate
        )
    )+0

 

3.jpg


Best regards,

 

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HELP-Dax-formula-for-Current-Streak.pbix
Message 5 of 7
1,598 Views
0
Anonymous
Not applicable
In response to v-lid-msft

‎03-19-2020 08:14 AM

This worked like a charm!  Thamnk you so much!

Message 6 of 7
1,579 Views
0
v-lid-msft
Community Support
Community Support

‎03-17-2020 03:28 AM

Hi @Anonymous ,

 

We can try to create a calculated column to get the consecutive days:

 

 

Consecutive Measure =
VAR LastUnderDate =
    CALCULATE (
        MAX ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] ),
        FILTER (
            'OSAT Streak (2)',
            'OSAT Streak (2)'[Under 75%] = 1
                && 'OSAT Streak (2)'[Hosp Num] = EARLIER ( 'OSAT Streak (2)'[Hosp Num] )
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]
                    <= EARLIER ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'OSAT Streak (2)' ),
        FILTER (
            'OSAT Streak (2)',
            'OSAT Streak (2)'[Under 75%] = 0
                && 'OSAT Streak (2)'[Hosp Num] = EARLIER ( 'OSAT Streak (2)'[Hosp Num] )
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]
                    <= EARLIER ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] >= LastUnderDate
        )
    )

 

 

If it doesn't meet your requirement, Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to One Drive For Business and share the link here.


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 3 of 7
1,614 Views
0
amitchandak
Super User
Super User

‎03-16-2020 08:21 PM

@Anonymous 

Can you share sample data and sample output.

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 2 of 7
1,631 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Kudoed Authors
View All