cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 CodeYearMonthParty TypeMerged
8872No Dis2020SeptD18872-D1
8872No Dis2020SeptD28872-D2
8872No Dis2020NovD28872-D2
8872No Set2020NovD18872-D1
       
       
       
       
  Year20202020  
  Risk September November Total  
  NO 222 
  DIS212 
  8872-D1111 
  8872-D21-1 
  SET-11 
  8872-D1-11 
  TOTAL 222 
1 ACCEPTED SOLUTION
westwrightj
Resolver III
Resolver III

Hey @Anonymous ,

 

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

 

Below is the sample data I am using

 

westwrightj_0-1605534772892.png

 

 

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]

 

westwrightj_1-1605534933006.png

 

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)

 

westwrightj_2-1605535583035.png

 

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

 

View solution in original post

3 REPLIES 3
westwrightj
Resolver III
Resolver III

Hey @Anonymous ,

 

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

 

Below is the sample data I am using

 

westwrightj_0-1605534772892.png

 

 

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]

 

westwrightj_1-1605534933006.png

 

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)

 

westwrightj_2-1605535583035.png

 

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!! 

MFelix
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors