DAX and distinct count filter context help

[Guitarmageddon]

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]

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]

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.

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?