Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AussieSusan
Frequent Visitor

Display longest run of same value

(New Power BI desktop user but long time programmer/Excel user.)

 

The underlying data set is a set of rows, one for each monthly status report. It contains the project ID, month/year of the report, and set of 'traffic light' statuses. For example:

ID      Month     Schedule    Finance

C1  March 2019     G               A

C1  April 2019        A              A

C1  May 2019         A              G

C2  March 2019      R             R

C2  April 2019         G             R

C2   May 2019         A             R

 

I have created a matrix that has the project as the row and the monthly (say) Schedule status as the columns.

What I want to do for each row (project) is to show the number of months, starting with the most recent (right-hand most), that have the same status as the most recent report.

 

What I'm looking for is which projects have been reporting a run of A or a R statuses for the last few months.

 

From the above example, C1 would show that the schedule has been A for the last 2 months, and C2 finance has been R for the last 3 months as well as C2 schedule as A for 1 month. (If I also get to see the run of G then that is not a probelm - I can ignre those).

 

I'm not really familiar with the various functions that are available and I'm reall y not sure where to start. So any assistance would be appreciated.

 

Susan

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@AussieSusan ,

 


I have created a matrix that has the project as the row and the monthly (say) Schedule status as the columns.

What I want to do for each row (project) is to show the number of months, starting with the most recent (right-hand most), that have the same status as the most recent report.

 

What I'm looking for is which projects have been reporting a run of A or a R statuses for the last few months.

 

From the above example, C1 would show that the schedule has been A for the last 2 months, and C2 finance has been R for the last 3 months as well as C2 schedule as A for 1 month. (If I also get to see the run of G then that is not a probelm - I can ignre those).


I'm not clear about the meaning of rows and columns you mentioned. In addtion, could you also clarify more about "show the number of months, starting with the most recent (right-hand most)"?

 

Regards,

Jimmy Tao

Thank you for your interest in my question. The real matrix looks like the first image.

The 2nd image highlights the values I'm trying to count for a particaulr project. As you can see the latest (the 'right-most column) is an 'A' and I want to know that there is a run of 8 'A' for this project.

(By the way, if a project does not have a value i the right-most column, then it can be skipped - the project has ended. However if the solution involves knowing the latest recorded value and using that, then no problem. I.e. knowing that project C00534 has a run of 5 'A's is not an issue.)

While I'm really only interested in the 'A' and 'R' values, getting a count of he run of statuses for any status is OK (i.e. knowing that C00513 has a run of  'G's is not a problem).

Susan

Raw dataRaw data
Count highlighted 'A's for this projectCount highlighted 'A's for this project

I take it from the lack of response that this is not possible with Power BI?

Susan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.