cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Distinct Count based on Most Recent Date

Hello, I am trying to find the distinct count for each party type and ID by risk. I deciced to merge the Party Type and ID columns (merged) and calculate the distinct count of this new, merged column, and then use a matrix table with risk as the rows and date for columns. Please see table below as an example. The measure for value is:

Merge Count = DISTINCTCOUNT(DISP_HISTORY[Merged])

My problem now is that if I do distinct counts by month, I have IDs and Party types that may cross months, but I want it to calculate based on the MOST RECENT date. So, in September, the count may be 2 (one for each type), but in November, the September count will now be 0 and the November count will be 2 (one for DIS 8872-D2 and one for SET 8872-D1).

 ID Risk Code Year Month Party Type Merged 8872 No Dis 2020 Sept D1 8872-D1 8872 No Dis 2020 Sept D2 8872-D2 8872 No Dis 2020 Nov D2 8872-D2 8872 No Set 2020 Nov D1 8872-D1 Year 2020 2020 Risk September November Total NO 2 2 2 DIS 2 1 2 8872-D1 1 1 1 8872-D2 1 - 1 SET - 1 1 8872-D1 - 1 1 TOTAL 2 2 2
1 ACCEPTED SOLUTION
Resolver III

Hey @Anonymous ,

I think I understand what you are trying to do here.

Below is the sample data I am using

The problem you identified makes complete sense, you are looking for distinct counts but want to assign each unique ID-Party Type combination to the most recent month

So first I'll perform a similar combined column as you mentioned

``````ID_Party Type =

'Sample Datas'[ID ]&"_"&'Sample Datas'[Party Type]``````

Have gone ahead in the query editor and changed values for "Sept" to "Sep"

Next we can create a makeshift date column based on what we have available

``````Date Column =

"01/" & ('Sample Datas'[Month]) & "/" & 'Sample Datas'[Year]``````

Then we can change the column type to "Date"

Next we can 'rank' the ID_Party Type combinations by date so the most recent is always ranked first

``````Ranked_ID Party Type =

var ID_PartyType = 'Sample Datas'[ID_Party Type]

return

RANKX(
FILTER('Sample Datas', 'Sample Datas'[ID_Party Type] = ID_PartyType),
'Sample Datas'[Date Column],
'Sample Datas'[Date Column], DESC, Dense)``````

Now we if use either of the below measurements if you want to count unique ID or unique Party-ID combinations

``````Distinct Count of ID =

CALCULATE(DISTINCTCOUNT('Sample Datas'[ID ]), 'Sample Datas'[Ranked_ID Party Type] = 1)

Distinct Count of Party-ID =

CALCULATE(DISTINCTCOUNT('Sample Datas'[ID_Party Type]), 'Sample Datas'[Ranked_ID Party Type] = 1)``````

In this case you can use the date coulmn we made earlier for the columns.

Let me know if this solution works for you or if we need to tinker a bit more

3 REPLIES 3
Resolver III

Hey @Anonymous ,

I think I understand what you are trying to do here.

Below is the sample data I am using

The problem you identified makes complete sense, you are looking for distinct counts but want to assign each unique ID-Party Type combination to the most recent month

So first I'll perform a similar combined column as you mentioned

``````ID_Party Type =

'Sample Datas'[ID ]&"_"&'Sample Datas'[Party Type]``````

Have gone ahead in the query editor and changed values for "Sept" to "Sep"

Next we can create a makeshift date column based on what we have available

``````Date Column =

"01/" & ('Sample Datas'[Month]) & "/" & 'Sample Datas'[Year]``````

Then we can change the column type to "Date"

Next we can 'rank' the ID_Party Type combinations by date so the most recent is always ranked first

``````Ranked_ID Party Type =

var ID_PartyType = 'Sample Datas'[ID_Party Type]

return

RANKX(
FILTER('Sample Datas', 'Sample Datas'[ID_Party Type] = ID_PartyType),
'Sample Datas'[Date Column],
'Sample Datas'[Date Column], DESC, Dense)``````

Now we if use either of the below measurements if you want to count unique ID or unique Party-ID combinations

``````Distinct Count of ID =

CALCULATE(DISTINCTCOUNT('Sample Datas'[ID ]), 'Sample Datas'[Ranked_ID Party Type] = 1)

Distinct Count of Party-ID =

CALCULATE(DISTINCTCOUNT('Sample Datas'[ID_Party Type]), 'Sample Datas'[Ranked_ID Party Type] = 1)``````

In this case you can use the date coulmn we made earlier for the columns.

Let me know if this solution works for you or if we need to tinker a bit more

Anonymous
Not applicable

Thank you so much!! This is VERY helpful, and very well written. I really appreciate it!!

Super User

Hi @Anonymous ,

What is the expected result? Do you want to count only values on the month were they appear or having a count until they change value?

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!