Disctinct count the number of Items with criteria using the latest assessment date

[arviaus]

Hi all,

very new to Power BI and just starting to use measures. What would be my formula if I want to show a visual that shows items with a fail result using their latest assessment date.

Department	Item	Assessment date	Assessment Result
1	a	1/01/2021	Pass
1	a	1/01/2022	Pass
1	b	1/01/2021	Fail
1	b
1	c	1/01/2021	Pass
1	c	1/01/2022	Fail
2	a	1/01/2021	Pass
2	a	1/01/2022	Pass
2	b	1/01/2021	Pass
2	b	1/01/2022	Pass
2	c	1/01/2021	Fail
2	c	1/01/2022	Fail
3	a	1/01/2021	Fail
3	a	1/01/2022	Pass
3	b	1/01/2021	Fail
3	b	1/01/2022	Fail
3	c	1/01/2020	Pass
3	c	1/01/2021	Fail

An output using above which I wanted to show using bar graph as follows

Department	Count of Fails
1	2
2	1
3	2

Thanks.

[Fowmy]

@arviaus 

Please verify your expected output. 

Example:
Dep 3 should give you 3 

[arviaus]

No, Dep 3 should be 2, because item a's latest assessment is 2022 and is a Pass.

[Fowmy · Accepted solution]

@arviaus 

Use this measure:

Count = 
VAR __T = 	
	ADDCOLUMNS(
		SUMMARIZE( Table03 , Table03[Department] , Table03[Item]),
		"Status" , 
		VAR __MaxDate = CALCULATE( MAX( Table03[Assessment date] ) )	
		VAR __Result = CALCULATE( MAX( Table03[Assessment Result] ) , Table03[Assessment date] = __MaxDate )
		RETURN
			INT( __Result = "Fail" )
	)
VAR __Result = 
	SUMX( __T , [Status] )
RETURN
	__Resul

[Ritaf1983]

Hi @arviaus 
Use dax code :

Fails = calculate( distinctcount('yourtable'[Item]), 'yourtable'[ Assessment]="Fail" )

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

[arviaus]

Thanks for the reply; however, this does not take into account the latest date for each item.