Assigning values to a measure based on conditions and getting count of rows

[azeem_3a]

I have a table like as shown below. 

if both KPI and Impacts are stable overall stability becomes Both Stable. if both KPI and Impacts are nor stable overall stability becomes Both not Stable. if KPI stable and Impacts not stable overall stability becomes only kpi stable. if KPI not stable and Impacts stable overall stability becomes only impacts stable.

my requirement is based on the selected period (eg- 1st Jan 2019 - 1st June 2019), it needs to summarize the overall stability of the Dept. that is, if any month in the selected period becomes Not stable, overall stability of the Dept should be not stable, else if any month in the selected period becomes only Kpi stable, overall stability of the Dept should be only kpi stable, else if any month in the selected period becomes only impact stable, overall stability of the Dept should be only impact stable. if none of the months the overall stability becomes not stable, it should be both stable.

I need to get two a table like this. 

Dept wise what is the stability and count of dept with overall stability case. And this two tables should be changing based on the filters used.

Thanks.

[Nathaniel_C]

Hi @azeem_3a ,

Please see the file and picture below.  The department filter appears to be working.  Still working on the date filter.

Rereading your description, and comparing it to the pictures that you posted, there is a little discrepancy between the two.  The logic that I used is as follows:

If ANY of the months have Both Not Stable, then the Dept Overall is Both Not Stable.

If there are NO Both Not Stable and there are ANY Impact Stable, then the Dept Overall is Impact Stable

If there are NO Both Not Stable or Impact Stable and there are ANY KPI Stable, then the Dept Overall is KPI Stable

If there are ONLY Both Stable, then the Dept Overall is Both Stable

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Stability PBIX

[Nathaniel_C]

Hi @azeem_3a ,

Stability = SWITCH(MIN('Table'[Status]),"Both Not Stable","NS","KPI Stable", "KS","Impact Stable", "IS", "Both Stable", "BS", "N/A")

What you need is a switch function like above.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Hi @azeem_3a ,

This is a switch measure which is a compressed version of the nested if statement which you outline in your logic.  You went from worst to best scenario, so we put the worst as the first test, and the best as the last. If the first one is true, that is where it stops and so forth.

Will get back to you on the count.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[azeem_3a]

Can you let me know the logic behind this measure. And also how to get the row count of each categories

Thanks