Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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
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
Thank you so much!! This is VERY helpful, and very well written. I really appreciate it!!
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êsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |