Forum Discussion
Dax Count concurrent identical values
I have a table of projects . These have overall status reports generated every week . I need to count the concurrent status from the last report going backwards Note different projectfor each project . So if for a particular project ,the status has been the same for 5 weeks I need to show 5 .
Thanks
Hi , Fisask
According to your description, you want to group by [ALL.project] and show the maximum number of consecutive occurrences of the same [ALL.overall_health]. Right?
Here are the steps you can follow:
(1) This is my test data :
(2) You can click "New column" to create two calculated columns:
flag = var _current_pro='Test'[ALL.project] var _pro_max_date= MAXX( FILTER('Test', 'Test'[ALL.project]=_current_pro ) , [ALL.as_on]) var _pro_color=MAXX( FILTER('Test','Test'[ALL.project]=_current_pro && 'Test'[ALL.as_on] = _pro_max_date),[ALL.overall_health] ) return IF('Test'[ALL.overall_health]=_pro_color ,1,-1)Count = var _current_pro='Test'[ALL.project] var _pro_m1=MAXX( FILTER( 'Test' , 'Test'[ALL.project]=_current_pro && 'Test'[flag]= -1 ),[ALL.as_on]) return SUMX( FILTER( 'Test','Test'[ALL.project]=_current_pro && 'Test'[ALL.as_on] >_pro_m1 ),[flag])(3)Then we can put the [Count] and the [ALL.project] field in the table visual.The result is as follows:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
Not clear. Please make it more understandable. Thanks.
How to Get Your Question Answered Quickly - Microsoft Power BI Community
Hi, I will attempt to make my problem clearer. In a database table I have status reports for about 200 projects. These are produced every week for each project . The status is a colour , Red, Amber or Green.
I simply want to show in my dashboard the number of weeks, going backwards in time from the current week, the status has remained the same for each project. So if a project status is Green this week and has been Green for the last 4 weeks .I want to display that the project has been Green for 4 weeeks .sounds simple but it's counting consecutive identical values and I can't solve it.
many thanks
Hi , Fisask
According to your description, you want to group by [ALL.project] and show the maximum number of consecutive occurrences of the same [ALL.overall_health]. Right?
Here are the steps you can follow:
(1) This is my test data :
(2) You can click "New column" to create two calculated columns:
flag = var _current_pro='Test'[ALL.project] var _pro_max_date= MAXX( FILTER('Test', 'Test'[ALL.project]=_current_pro ) , [ALL.as_on]) var _pro_color=MAXX( FILTER('Test','Test'[ALL.project]=_current_pro && 'Test'[ALL.as_on] = _pro_max_date),[ALL.overall_health] ) return IF('Test'[ALL.overall_health]=_pro_color ,1,-1)Count = var _current_pro='Test'[ALL.project] var _pro_m1=MAXX( FILTER( 'Test' , 'Test'[ALL.project]=_current_pro && 'Test'[flag]= -1 ),[ALL.as_on]) return SUMX( FILTER( 'Test','Test'[ALL.project]=_current_pro && 'Test'[ALL.as_on] >_pro_m1 ),[flag])(3)Then we can put the [Count] and the [ALL.project] field in the table visual.The result is as follows:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you so much . The logic is very clear . I still have an issue but I think I can work it out from here
