I am working with a table similar to the below:
| ID | Date | Category |
| 123 | 12 Feb, 2021 | A |
| 123 | 10 Feb, 2021 | B |
| 456 | 20 Mar, 2021 | B |
| 456 | 22 Mar, 2021 | A |
| 456 | 22 Mar, 2021 | B |
| 789 | 25 Feb, 2021 | A |
I want to display the distinct count of the ID field by category. However, some ID's fall into two different categories so the column totals do not add up to the distinct total value.
Currently my result looks something like
| Category | Distinct Count |
| A | 3 |
| B | 2 |
| TOTAL | 3 |
Since different instances of an ID can fall into more than one category, the column totals do not add up to the correct distinct total amount. As seen from the first table, ID 123 and ID 456 fall into two different categories so their count is added twice - one for each category. Thus manually adding the Distinct Count column (5) is greater than the distinct total (3).
In this context, I am only interested in the Category of the minimum date Date of each ID. So I tried the following measure to return the distinct count of each ID, for only their associated minimum dates.
Measure =
CALCULATE (
DISTINCTCOUNT ( Table[ID] ),
FILTER ( ALL ( Table[Date] ), MIN ( Table[Date] ) )
)
I then plotted this measure against the categories and still received the same result as before. I know something is wrong with the FILTER portion of the measure, perhaps the ALL call, but I have not been able to get something to work.
