Count measure needed to keep Closed Status and remove Decommissioned Status

Hi Anonymous 

Firstly let‘s transform the Table. I add some data in 2020 and 2019 into the table.

Then create two calculated columns in this table:

Year Column:

Year = YEAR('Table'[FirstStatusChangedDate])

Month Column:

Month = MONTH('Table'[FirstStatusChangedDate]).

Then we create two measures:

Measure1 for filtering table:

Filter = IF(MAX('Table'[State])="Decomissioned"||MAX('Table'[Validated])="Out of Scope",1,0)

Drag Filter Measure into Filters on this visual：

Then select is not 1 and apply filter：

Result：

Measure2 for count the result:

First we need to create a new table(Slicer) use the Year Column values:

Slicer = VALUES('Table'[Year])

We can use Slicer table to create a Slicer.

Measuer2:

Count =

var t = FILTER('Table','Table'[State]<>"Decomissioned"&&'Table'[Validated]<>"Out of Scope")

VAR _a = SELECTEDVALUE('Slicer'[Year])

return

CALCULATE(DISTINCTCOUNT('Table'[Month]),Filter(t,[Year]=_a))

Let’s build a card visual by Count Measure.

Now we can have a test, let’s select 2020 in Slicer, result is as below:

Due to i don't know whether you will count by Title or Tag. Here I only count by month. Just like above I select 2020 in Slicer. And the month value in 2020 which state= closed and validated ≠Out of Scope is 3,1,1. So the result is 2.

If this reply still can't solve your problem please provide me with more details about what the value you will count by (Title, Tag,Month or Complex) and the visual of the result you want.

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/EdqAm2WcAxNCunxHy44vpEsBoOEzzTWRiahofQCGhEMKMw?e=lkZeBw

Best Regards,

Rico Zhou

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