DAX measure to apply DISTINCTCOUNT before filters?

[sritzma]

Apologies if this has been gone over before but I haven't been able to successfully apply any of the results when searching.

Example table:

Name	Date	Category
Matt	1/1/2019	B
Mark	1/1/2019	A
John	1/2/2019	B
John	1/1/2019	A
Mark	1/2/2019	A
Mark	1/3/2019	C

I need a measure (to account for date and other filtering) that will count how many distinct persons are in a given category counting only the last/latest entry from the date columm.

Wanted results:

Avocado	Blue	Cherry	Total
0	2	1	3

Results with DISTINCTCOUNT:

Avocado	Blue	Cherry	Total
2	3	1	3

As far as I understand DISTINCTCOUNT it applies a filter first, THEN applies distinct logic. If I could apply the distinct logic first I would just sort my table by date then do that, then apply the filter. Easy in Excel or Power Query, but I'm not getting it with DAX yet! Any help would be greatly appreciated. Is this a SUMMARIZE scenario or ?

[Ashish_Mathur]

Hi,

I do not understand.  Why should the answer for Category A (Avocado) be 0?

[sritzma]

For each person with an A (Avocado) entry there also exists a second (or third) entry with a later date than the A (Avocado) entry. I would like to only take the entry with the latest date. That said, I found that my full data has same-dates so I ended up using an index column.

Even then, because I want to effectively apply date slicers, then a distinct argument, THEN other slicers, I don't think this will work as I wish it to. As much as I'd like to allow full date selectionI think I will have to limit this measure to certain quarters or fiscal years etc and have a seperate table for each.

[Ashish_Mathur]

Hi,

I really cannot understand your question.  Perhaps someone else will help you.

[Greg_Deckler · Accepted solution]

Difficult to be exact with the information given. But, assuming your data table looks like what you describe below, try something like this:

 

Measure CountDistinct = 

    VAR __category = MAX([Category])

    VAR __table = ALL('Table')

    VAR __table1 = ADDCOLUMNS(__table,"__maxDate",MAXX(FILTER(__table,[Name] = EARLIER([Name])),[Date]))

    VAR __table2 = FILTER(__table1,[Date] = [__maxDate] && [Category] = __category) 

    RETURN COUNTROWS(DISTINCT(SELECTCOLUMNS(__table2,"__Name",[Name])))

[sritzma]

I ended up using an index column rather than the date column but other than that it worked a treat. Thanks!

Modified formula I ended up with:

Measure Unique = 

    VAR __category = MAX([Category])

    VAR __table = ALL('Table')

    VAR __table1 = ADDCOLUMNS(__table,"__maxIndex",MAXX(FILTER(__table,[Name] = EARLIER([Name])),[Index]))

    VAR __table2 = FILTER(__table1,[Index] = [__maxIndex] && [Category] = __category) 

    RETURN COUNTROWS(DISTINCT(SELECTCOLUMNS(__table2,"__Name",[Name])))

Total still doesn't quite work right but there's easier ways of calculating that. 

EDIT: THIS METHOD DOES NOT ALLOW FILTERING

[sritzma]

I take it back! This method does not allow any use of slicers/visuals etc because of the ALL function 😞 

Edit: I realize now that I essentially want to have Power BI apply any date slicers, THEN get the distinct count, THEN apply other slicers. This is probably not doable. Thanks again.

[sritzma]

Thank you for your formula. Although it works for the small set of data I provided it does not seem to scale up when provided with my real dataset, unfortunately. 

With a larger subset of (fake) data it should show the following (as verified through excel, deleting duplicates after sorting by date):

Blank	Avocado	Blue	Cherry	Total
42	439	632	385	1498

Instead it shows (Column total is 1519 and Total does not work) :

Blank	Avocado	Blue	Cherry	Total
42	443	639	395	395

As a newbie I am not allowed to upload yet but here is a Drop Box link to the file:

https://www.dropbox.com/s/14omwmkvj78jpjz/test.pbix?dl=0

If possible, I was also hoping to have this measure be independent of the category column - I have many other categories to slice and dice by in the full set of data. Gender and Race are included in this test file as other examples I'd like to filter by.

Your advice has given me some ideas that I haven't been able to quite make work yet but I'm trying! Thanks again.

edit: I see now why your formula was not quite enough for my expanded data set - it assumes only one entry per date. Still working through it :D.