COUNTROWS for each unique value of a measure

[Jazmine]

Hello - 

I am trying to solve how to count consecutive months of percentage less than or equal to -10%. I have identified where it is -10% and am now trying to get where its -10% for 6 or more consecutive months. In the below, I have taken multiple steps to identify the consecutive months but am now having trouble counting the rows based on that measure. Is it possible to count based on a measure or is there an easier way to identify/count consecutive months?

Orange columns are columns in the table FreightDB. Blue columns are the measures that I have created. Count Consecutives measure is what I am trying to create but every attempt has failed to get the desired results because am unable to partition by the O2D column and each distinct measure value.

DAX for measures created:

6 Months Indicator = IF ( [Count of 6 Months Over] >= 6,

                                         [% DAT Variance],

                                         BLANK( )

                                       )

Month Integer = IF ( [6 Months Indicator] <> BLANK( ),

                                 AVERAGE ( 

                                                  FreightDB[Ship Date Month Int] ) * 1,

                                 BLANK ( )

                            )

Month Integer Rank = IF ( [6 Months Indicator] <> BLANK( ),

                                            CALCULATE (

                                                     RANKX (

                                                          ALL ( FreightDB[Ship Date Month Int] ), [Month Integer], ,ASC )

                                             ),

                                          BLANK ( )

                                     )

Month ID = [Month Integer] - [Month Integer Rank]

 

Count Consecutives = IF ( [6 Months Indicator] <> BLANK( ),

                                          COUNTX (

                                               VALUES (

                                                              FreightDB

                                                             ),

                                                               [Month ID]

                                                        ),

                                             BLANK ()

                                        )

 Thanks for any help in advance!

[v-eqin-msft]

Hi @Jazmine ,

Any updates?

Best Regards,
Eyelyn Qin

[Jazmine]

@v-eqin-msft  no updates. Posting a link to a drive is not an option as anybody can access. Thanks!

[v-eqin-msft]

Hi @Jazmine ,

If you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Jazmine ,

Since you did not provide me all fields for the 3 measures you created.( Like [Count of 6 Months Over] ,[% DAT Variance]).   I just create some dummy to test.

Please try:

Measure = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),[O2D]=MAX('Table'[O2D]) && [Month ID]=MAXX('Table',[Month ID])))

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

[Jazmine]

Hi @v-eqin-msft  - 

Thanks for taking a look into this! Those are the results that I am looking for but when I apply it does not give same results for some reason. Will send a DM with the PBI sample file. I created the measure with your formula as 'Evelyn Measure'. I tried to tweak it a lil but put it back to the original formula. 

[MFelix]

Hi @Jazmine ,

There are a couple of things in your model that I don't understand:

• You refer you want to count the values that are consecutive below -10%  but what is this colum of the percentage? is it the six month indicator?
• Also you are refering to the [6 Months Indicator] is this the column you present on top in orange or is it a metric? (asking this because ussually columns are preceded by the table name.

Also based on the values you have what is the result you want on  the sample you provided? 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Jazmine]

Hi @MFelix  -

Thanks for taking the time to look at my issue! Will try to answer the questions below and will also DM the sample PBI created with subject as same title for this thread.

• You refer you want to count the values that are consecutive below -10%  but what is this colum of the percentage? is it the six month indicator?  There are actually 2 percentage columns but all were not in the screenshot. The PBI file has all columns that were created. One column has the % difference between rates. The six month indicator is an IF function of identifying where an O2D SCAC has a total of at least 6 months <= -10% . 
• Also you are refering to the [6 Months Indicator] is this the column you present on top in orange or is it a metric? (asking this because ussually columns are preceded by the table name. [6 Months Indicator] is a measure

Also based on the values you have what is the result you want on  the sample you provided?  I am trying to identify where an O2D SCAC and Year has at least 6 consecutive months of <= -10%. Right now I only have where its a total of at least 6 months. I tried to achieve this by ranking the months where it was at least 6 Months total and then subtract that ranking number from the original month integer to get where the consecutives are happening. But now i need to be able to total those consecutives for each O2D SCAC and Year and have not been able to figure out how to do that. [Count of Consecutives] measure has changed many times but I cannot find a solution to partition by a measure or another workaround. Once the consecutive total for each O2D SCAC and Year is calculated, the MAX number can then be pulled from that measure to find out whether its 6 consecutive instead of 6 total.

Thanks!