COUNT DISTINCT users ID, but only select their last result

[NumeroENAP]

Hi, 

I have someting of the kind :

UserID	Name	DT_EVAL	Conformity
Markov67	Andrei Markov	2020-08-10	conform
Markov67	Andrei Markov	2020-08-08	not conform
Plekanec14	Thomas Plekanec	2020-08-10	not conform

I want my measure to return me : 2

Reason : I only want the last evaluation of every user. 

Thanks

[Greg_Deckler]

@NumeroENAP - Perhaps use MAX to get the greatest date in the table and then FILTER down to that date, use DISTINCT and COUNTROWS, so like:

Measure = COUNTROWS(DISTINCT(FILTER('Table',[Date] = MAX('Table'[Date])))

[NumeroENAP]

Ok, it's nice, and it's defenitely the closest I've been from the solution, but, it's not exactly what I need.

By exemple, if I need to group it by conformity :

Conformity	Distinct User
Conform	1
Not conform	2

But, with what you gave me, even if it gives me a table with what I need (raw), but I can't group it the way I want.

Am I clear enough?

Thanks!

[Greg_Deckler]

@NumeroENAP - Wait, now I am confused, where did conform/not comform come from? Perhaps we should back-up. Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[NumeroENAP]

I need to have a measure that tells me exactly :

• How many employees have a «conform» or «not conform» evaluation at their last evaluation ;
• I also need that measure to be flexible, so I can use it in others visualisation, with other columns of the same table.  

UserID	Name	DT_EVAL	Conformity
Markov67	Andrei Markov	2020-08-10	Conform
Markov67	Andrei Markov	2020-08-08	Not conform
Plekanec14	Thomas Plekanec	2020-08-10	Not conform

Thanks!

[Anonymous]

// You have to have a Calendar table
// that's DISCONNECTED from the T table.
// Now, this is a measure that for a
// selected period of time from Calendar
// tells you the number of 'Conform'
// emps at their last evaluation BEFORE OR ON
// the last day of the selected period.
// I assume an employee is fully identified
// by UserID and that each employee can only
// have 1 entry on any specific day. This
// measure does fully respond to slicers, so
// be careful to correctly interpret the results.

[# Conform Emps] =
var __lastVisibleDate = MAX( Calendar[Date] )
var __empsWithConformAsLastEval =
	FILTER(
		VALUES( T[UserID] ),
		var __latestRecordForUser =
			CALCULATETABLE(
				TOPN(1,
					filter(
						T,
						T[DT_EVAL] <= __lastVisibleDate
					),
					T[DT_EVAL],
					DESC
				)
			)
		var __conformityIsConform =
			NOT ISEMPTY(
				FILTER(
					__latestRecordForUser,
					T[Conformity] = "conform"
				)
			)
		return
			__conformityIsConform
	)
return
	COUNTROWS( __empsWithConformAsLastEval )

[NumeroENAP · Accepted solution]

In the end, I went with a non-performing option, but it works great. 

First of all, I created a calculated column : 

LAST_EVAL_DT = VAR LastID = RES[ID]

RETURN

MAXX(

FILTER( ALL('RES') , 'RES'[ID] = LastID) , 

'RES'[DT_EVAL])

After that, I created a column that I could filter easily with a COUNTROWS :

QC = IF (RES[DT_EVAL] = RES[LAST_EVAL_DT] , 1 , BLANK() )

Thank you all for your efforts.