The last status by person in a scope of all data

[Anonymous]

Hi folks,

I desperately need help. 

There is a visualization that shows certified vs not certified people by date:

Here is the problem: if a person X has both statuses in April, for example, then I get double count of this person:

What I need is the last person's status by date (so if he was certified in April then he has only certified status in April).

I've tried the following measure that seems to be working:

Test_Part =

VAR A =

CALCULATE (

MIN ( 'Table'[Certification Status] ),

ALLSELECTED ( 'Table'[Certification Status] )

)

RETURN

CALCULATE (

[# of people],

KEEPFILTERS(

'Table'[Certification Status] = A

)

)

But when it shows the whole data, then it takes min status as certified for everyone in every month

I've tried to use max date, allexcept per person - nothing is working.

Appreciate any help.

Thank you!

[Anonymous · Accepted solution]

// I assume that everything is in one
// table. However, this is such a BAD DESIGN
// that it will in the end make you cry
// crocodille's tears if that's the case.
// You should always follow Best Practices
// to make sure that correct formulas will
// always return correct numbers. If you don't
// follow them, you can never be sure if
// the numbers are reliable. You've been
// warned. Please transform one-table design
// into a correct star schema and adjust
// the formula below accordingly.

[Count by LAST Status] =
var SelectedStatuses = 
	DISTINCT( 'Table'[Certification Status] )
var PeopleAndTheirLastStatusInTimeframe =
	ADDCOLUMNS(
		DISTINCT( 'Table'[PersonID] ),
		"@LastStatus",
			CALCULATE(
				MAXX(
					// This TOPN should return 1
					// row if it's true that no
					// person can have different
					// statuses on a single day.
					// If this assumption is not
					// true, you have to make sure
					// that this function returns
					// the row with the last status
					// in the visible timeframe.
					TOPN(1,
						'Table',
						'Table'[Date],
						DESC
					),
					'Table'[Certification Status]
				),
				// You need to remove any filter on this
				// column if you want to get the last
				// status in the timeframe.
				ALL( 'Table'[Certification Status] )
			)
	)
var PeopleCount =
	COUNTROWS(
		FILTER(
			PeopleAndTheirLastStatusInTimeframe,
			[@LastStatus] in SelectedStatuses
		)
	)
return
	PeopleCount

[edhans]

We need some data, or a sample PBIX file of your work so far.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.