Distinct Count over Time Periods

[jcampbell474]

Hoping to get some ideas on the best way to get a distinct count that depends on the selected date range.

The user is currently doing a distinct count of the street address, per customer. Of course, there is a date column. ~200k records.

Using performance analyzer and DAX Studio, I looked at the model and query behind the visual. The street address cardinality is very high (~30% of the database) and the visual takes about a minute to process.

Feels like it's more of a modeling issue vs. optimal DAX. The model is just one table. I thought about creating an address dimension table and joining it using the customer number. But, don't have a clue on how to make it work (mostly due to the distinct count over a varying date range).

Anyone know how to do this? All help will be greatly appreciated!

[AntrikshSharma]

@jcampbell474 If you are using DISTINCTCOUNT (Table[Column] ) and you see high Storage Engine queries then I would suggest that you also try SUMX ( DISTINCT ( Table[Column] ), 1 ) construct.

I solved a similar problem a few days ago here : https://forum.enterprisedna.co/t/dax-calculation-based-on-previous-day-and-a-constant-value/14948

[jcampbell474]

Thank you.  We're actually using SUMX.  Viewing the query in DAX Studio converts it to DCOUNT.