# DAX and distinct count filter context help



## Guitarmageddon (Nov 13, 2018)

So I have just one table that I added to the data model, to give me some of the flexibility of dax and afford me some practice  time. I have a measure that is a distinct count on my "sites" column, to count unique store numbers in my data set. I would like the measure to ignore the filter context though, and no matter what site I may have the pivot table filtered down to, I want it to still reflect ALL my stores and not convert the distinct count to say, 1, once I filter. I think I need an ALL function here, but I cant quite grasp. can anyone give some tips? Currently its just =DISTINCTCOUNT([Site])


----------



## Misca (Nov 13, 2018)

I'd have the DISTINCTCOUNT as a separate measure and do this with two measures:

Measure1 = DISTINCTCOUNT(TableName[Site]) 

Measure2 = CALCULATE( [Measure1], ALL(TableName))

The two measure approach lets you use the same DISTINCTCOUNT-measure in other calculations as well and it makes the second measure easier to read & adjust when needed.


----------



## Guitarmageddon (Nov 14, 2018)

Misca said:


> I'd have the DISTINCTCOUNT as a separate measure and do this with two measures:
> 
> Measure1 = DISTINCTCOUNT(TableName[Site])
> 
> ...



Hmm thanks for helping. I did that like you said, however it still is obeying the main filter context of the table. I have "site" dragged into the filters part of my pivot table, and I have it to just one site currently. It only works when I unfilter back to ALL sites, just like the original count distinct acts. Any thoughts?


After some playing with it, I think I wasnt entirely clear with what I needed. I think I wanted to see count of distinct sites, regardless of filter, but within the context of another column called "generation."


=CALCULATE(Table1[DISTINCT SITE COUNT],FILTER(Table1,DISTINCTCOUNT([Gen])))

This one works just like it should, however, it filters back to all "1"s when I but a single site filter on the table. I know this probably is sounding confusing, I can provide a screenshot of the table construction if that would help? Or is what Im trying to do just not possible?


----------

