Can't figure out this simple DAX issue

[Anonymous]

I have what I believe is a simple issue to fix, I assume a MIN function is needed, but can't get the syntax right. I have a simple data set here, with fundraisers who can have multiple prospects, and the prospects are ranked. A prospect can be ranked by more than one fundraiser - which is the essense of my problem:

If a prospect is ranked differently by more than one fundraiser, then we want the prospect to be counted with the rating given by that fundraiser. But if we don't filter for fundraiser and show all, then we only want each prospect counted once, and we want to take the MIN(rank) for that prospect.

For example, I have this data:

If I filter my dashboard for fundraiser f4, I see the correct data, 1 item for ranks 1 and 2 (best/better):

But if I remove the filter to show all fundraisers, I get a total of 8 items, representing the 8 rows in my data. 

This isn't what I want, as I'm double-counting prospects 4 and 6. What I want is to aggregate the data so that I only get one row per prospect, and I use the min rank value. In this sample, I've added an extra "rating_all" column to show the value which should be used when we're showing all fundraisers. However, I don't think I want or need that column, all I really need is to always show the min value per prospect, and that may change depending on whether or not fundraiser is being filtered.

So this is what the chart should look like with no fundraiser being filtered:

The .pbix and data can be found here

Thanks!

[v-deddai1-msft]

Hi @Anonymous ,

What's your expected output? As I can see duplicate prospects for 4 and 6 ,but they belong to different fundraiser. Do you want to remove the duplicate value for prospects by use MIN function? You can use the following measure :

MINX(ALLEXECPT(fact,fact[prospect]),fact[rank])

But the slicer for fundraiser will not work well any more. So would you please explain more about what's your expected output? 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@Anonymous , Try distinctcount

distinctcount(Table[fundraiser])

[Anonymous]

I'm using distinct count, but I still have two rows in my data for a couple of the prospects, because they have different ratings:

For prospects 4 and 6, I need to show the min(rating) so that each is only counted once. But when I tried to create a measure usig MIN, I end up getting "1" as the value for everything, as it seems to be looking at the entire dataset to get MIN, rather than doing it per prospect. Does that make sense?