Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 
Need Help
  • westwrightj's avatar
    westwrightj
    5 years ago

    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

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    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?

  • westwrightj's avatar
    westwrightj
    Icon for Resolver III rankResolver III
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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