Count measure needed to keep Closed Status and remove Decommissioned Status

[Anonymous]

I have a table called Test Scripts.

I need a COUNT measure, which will keep a Test Script whenever the script State="Closed" and will remove the Test Script whenever the State="Decomissioned" OR Validated="Out of Scope".

Then, I need it to only display all the changes ref to the lastest FirstStatusChangedDate (e.g.: latest TAG changes, lastest Title changes,etc).

Current table:

Test Script ID	State	Validated	Tags	Title	FirstStatusChangedDate
6	Closed	Not Analysed	GLS_North; IA; UFT; Vistex	NUSA-MS-BB02_VISTEX_BILLBACK_INDIRECT_OWNED	22/02/2017 15:14
6	Closed	Not Started	GLS_North; IA; UFT; Vistex	NUSA-MS-BB02_VISTEX_BILLBACK_INDIRECT_OWNED	22/02/2017 15:14
6	Closed	Ticket	AMS; GLS_North; IA; MATSPW31; UFT; Vistex	NUSA-MS-BB02_VISTEX_BILLBACK_INDIRECT_OWNED	22/02/2017 15:14
6	Closed	Validated	GLS_North; IA; UFT; Vistex	NUSA-MS-BB02_VISTEX_BILLBACK_INDIRECT_OWNED	22/02/2017 15:14
6	Closed	Validated	GLS_North; NA_MAT_Exec; UFT; Vistex	MS-NUSA-VTX_ART002_1_BB02	22/02/2017 15:14
6	Decomissioned	Not Analysed	AMS; GLS_North; IA; MATSPW31; MATSPW32; MATSPW33; MATSPW34; UFT; Vistex	NUSA-MS-BB02_VISTEX_BILLBACK_INDIRECT_OWNED	6/04/2020 12:55
6	Decomissioned	Not Analysed	AMS; GLS_North; IA; MATSPW31; MATSPW32; MATSPW33; UFT; Vistex	NUSA-MS-BB02_VISTEX_BILLBACK_INDIRECT_OWNED	6/04/2020 12:55
8	Closed		GLS_North; IA; UFT; Vistex	MS-NUSA-VTX_ART001_1_BB01	22/02/2017 15:14
8	Closed	Being Tested	AMS; GLS_North; IA; MATSPW31; UFT; Vistex	NUSA-MS-BB01_VISTEX_BILLBACK_INDIRECT_GPO	22/02/2017 15:14
8	Closed	Not Analysed	GLS_North; IA; UFT; Vistex	NUSA-MS-BB01_VISTEX_BILLBACK_INDIRECT_GPO	22/02/2017 15:14
8	Closed	Validated	GLS_North; NA_MAT_Exec; UFT; Vistex	MS-NUSA-VTX_ART001_1_BB01	22/02/2017 15:14
8	Decomissioned	Not Analysed	AMS; GLS_North; IA; MATSPW31; MATSPW32; MATSPW33; MATSPW34; UFT; Vistex	NUSA-MS-BB01_VISTEX_BILLBACK_INDIRECT_GPO	6/04/2020 12:55
8	Decomissioned	Not Analysed	AMS; GLS_North; IA; MATSPW31; MATSPW32; MATSPW33; UFT; Vistex	NUSA-MS-BB01_VISTEX_BILLBACK_INDIRECT_GPO	6/04/2020 12:55
9	Closed		GLS_North; IA; UFT; Vistex	MS-NUSA-VTX_ART003_1_BB03	22/02/2017 15:14
9	Closed	Not Started	GLS_North; IA; UFT; Vistex	NUSA-MS-BB03_VISTEX_BILLBACK_INDIRECT_STREET	22/02/2017 15:14
9	Closed	Out of Scope	AMS; GLS_North; IA; UFT; Vistex	NUSA-MS-BB03_VISTEX_BILLBACK_INDIRECT_STREET	22/02/2017 15:14
9	Closed	Out of Scope	GLS_North; IA; UFT; Vistex	NUSA-MS-BB03_VISTEX_BILLBACK_INDIRECT_STREET	22/02/2017 15:14
9	Closed	Pending MKT	GLS_North; IA; UFT; Vistex	NUSA-MS-BB03_VISTEX_BILLBACK_INDIRECT_STREET	22/02/2017 15:14
9	Closed	Validated	GLS_North; NA_MAT_Exec; UFT; Vistex	MS-NUSA-VTX_ART003_1_BB03	22/02/2017 15:14
9	Decomissioned	Out of Scope	AMS; GLS_North; IA; MATSPW31; MATSPW32; UFT; Vistex	NUSA-MS-BB03_VISTEX_BILLBACK_INDIRECT_STREET	15/08/2019 14:36
32	Decomissioned	Not Analysed	CAMBAR	NPPC-FICO-Export_Customer_Delivery	6/02/2018 15:27
36	Closed	Not Analysed	GLS_North; IA; UFT; Vistex	NUSA-MS-BB04_VISTEX_BILLBACK_DIRECT_GPO	22/02/2017 15:14
36	Closed	Not Started	GLS_North; IA; UFT; Vistex	MS-NUSA-VTX_ART004_1_BB04	22/02/2017 15:14
36	Closed	Validated	GLS_North; IA; UFT; Vistex	NUSA-MS-BB04_VISTEX_BILLBACK_DIRECT_GPO	22/02/2017 15:14
36	Closed	Validated	GLS_North; NA_MAT_Exec; UFT; Vistex	MS-NUSA-VTX_ART004_1_BB04	22/02/2017 15:14
36	Decomissioned	Not Analysed	AMS; GLS_North; IA; MATSPW31; MATSPW32; MATSPW33; MATSPW34; UFT; Vistex	NUSA-MS-BB04_VISTEX_BILLBACK_DIRECT_GPO	6/04/2020 12:51
36	Decomissioned	Not Analysed	AMS; GLS_North; IA; MATSPW31; MATSPW32; MATSPW33; UFT; Vistex	NUSA-MS-BB04_VISTEX_BILLBACK_DIRECT_GPO	6/04/2020 12:51

What I want is to have a measure which will remove the State entries when State="Decomissioned" OR Validated="Out of Scope" and keep State="Closed".
e.g.: 
If Validated="Out of Scope", then remove this entry
If State=Decommissioned, remove this entry
If State=Closed and Validate=anything other than "Out of Scope", then keep and display the last updates (from FirstStatusChangedDate).

However, I can't use simple visual filter as my date slicer should COUNT whenever the script was Closed and then stop counting it whenever it gets decommissioned or out of scope. It's dynamic, which is why I need a measure.

So, if the script got Closed in Jan/20 and then got Decommissioned or Out of Scope in Mar/20, when the person selects the year 2020 in my slicer, it should count the script as Closed (in scope) for January, February and March (but only count once) and after March it should stop counting the script.
Which means, the measure will count as 1 script (the same script) between Jan and Mar/20.

Thanks

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table ,problems or share me with your pbix file from your onedrive business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

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.

[amitchandak]

@Anonymous , put a visual level filter and have all others like this other than date and date

put a viusal level filter for closed

Validated= lastnonblankvalue(Table[FirstStatusChangedDate],max(Table([Validated])))
Tags= lastnonblankvalue(Table[Tags],max(Table([Validated]))) //Title
last = lastnonblankvalue(Table[Tags],max(Table([Title]))) //Title

Or use this with all other values as not summarised

Measure =
VAR __id = MAX ( 'Table'[Script ID] )
VAR __date = CALCULATE ( MAX( 'Table'[FirstStatusChangedDate] ), ALLSELECTED ( 'Table' ), 'Table'[Script ID] = __id )
RETURN CALCULATE ( Count ( 'Table'[State] ), VALUES ( 'Table'[Script ID] ), 'Table'[Script ID] = __id, 'Table'[FirstStatusChangedDate] = __date )

[Anonymous]

No, this measure doesn't work as I need.

It is just showing the last State (last status).

What I want is to have a measure which will remove the State entries when State="Decomissioned" OR Validated="Out of Scope" and keep State="Closed".
e.g.: 
If Validated="Out of Scope", then remove this entry
If State=Decommissioned, remove this entry
If State=Closed and Validate=anything other than "Out of Scope", then keep and display the last updates (from FirstStatusChangedDate).

However, I can't use simple visual filter as my date slicer should COUNT whenever the script was Closed and then stop counting it whenever it gets decommissioned or out of scope. It's dynamic, which is why I need a measure.

So, if the script got Closed in Jan/20 and then got Decommissioned or Out of Scope in Mar/20, when the person selects the year 2020 in my slicer, it should count the script as Closed (in scope) for January, February and March (but only count once) and after March it should stop counting the script.
Which means, the measure will count as 1 script (the same script) between Jan and Mar/20.

thanks again for your help