COUNT unique values in a column after filter context?

DAX DAX Calculations
1

I’m having issues understanding the best way to use a DAX formula to count the unique # of values in a column once filtered… Here is an example table:

example-table
example-table896×306 15.2 KB

The Unique Donor ID counts the ‘consolidated donor household or unit’ of what it is I want to distinctly count as a single unit so that in the context of the filters I have on my report page. In this snapshot, the count of the Unique Donor ID should be 4. When I filter on my report page, any COUNT formulas I use don’t actually filter based on the row context so I’m not sure I’m understanding how to do this correctly.

I am trying to count “# of Donors” and use the Unique Donor ID to count how many donors there are in the context. Does that make sense?

2

@dcads,

I’m not positive I understand the scenario fully, but let me know if this works:

Unique Donor ID Count = 
COUNTROWS(
    VALUES( Data[Donor ID] )
)

image

Hope this is helpful. Full solution file attached.

  • Brian

eDNA Forum - Unique Donor ID Solution.pbix (18.6 KB)

1 Like
3

Hi Brian - thanks. I think with more context the formula needs to be modified. If there is a 1 to many relationship from the Donor Data file sample I provided and another table called Gifts that has historical gift data from 5 years on the column “Unique Constituent ID” how does this affect the filtering? I tried this formula and the count doesn’t change when I change the filter context on my visualization.

I want to be able to filter gift data by various things like channel, etc. and have the donor count change based on Unique Donor ID (not unique constituent ID). I want to upload a sample file but would have to anonymize a lot of the data so let me know what might be the best approach.

Thanks again!

4

@dcads,

I’m confident I can produce a good solution for you on this, but I’m definitely going to need two things: 1) an anonymized PBIX file to work with, so that I can grasp the whole picture, including your current measures and data model (often “DAX” problems are really issues with the data model); and 2) a mockup (Excel, PowerPoint or even hand-drawn sketch) of the final results/outcome you want to see.

Below is a video I put together on quick and easy techniques for anonymizing confidential data to allow posting on the forum.

Please let me know if you have any questions.

Thanks.

  • Brian
1 Like
5

Hi @dcads, we’ve noticed that no response has been received from you since the 31st of July. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!