Count of stores that are selling only 1 product for a specific date range.

[Anonymous]

I have been struggling with this question for a few days. I can not share the data as it is not my data. To best try to explain the data. I have an extensive data set with columns of Date, Store number, and Product number. 

I need to know how many stores only have 1-9 products and have the ability to slice by date. Any suggestions are appreciated.

I need to generate a table that looks something similar to the table below:

[daXtreme · Accepted solution]

// You first have to create a table with
// all the numbers of distinct products.
// This will be a dimension table disconnected
// from any other. Here's how to create this
// via DAX (but should be in Power Query).

[# Distinct Products] = // calculcated table
var MaxNumberOfDistinctProducts =
    MAXX(
        VALUES( YourTable[Store Number] ),
        CALCULATE(
            // assuming Product Number identifies
            // a product uniquely
            DISTINCTCOUNT( YourTable[Product Number] )
        )
    )
 var Output = 
    SELECTCOLUMNS(
        GENERATESERIES( 0, MaxNumberOfDistinctProducts, 1 ),
        "# Dist Prods", [Value]
    )
 return
    Output
    
    
// Once you've got this... here's the measure:

[# Stores w/ # distinct prods] = // measure
var SelectedCounts = VALUES( '# Distinct Products'[# Dist Prods] )
// Bear in mind this measure obeys all currently
// active filters. This is important to understand.
var StoresWithDistProdCounts =
    FILTER(
        VALUES( YourTable[Store Number] ),
        CALCULATE(
            DISTINCTCOUNT( YourTable[Product Number] ) IN SelectedCounts
        )
    )
var Output = COUNTROWS( StoresWithDistProdCounts )
return
    Output
    
// By the way, please make sure your model does not have referential
// integrity problems. You can check for this using DAX Studio. This
// code has been written without actually runnig it over a model, so
// there might be some typos or similar issues. Please resolve them
// on your end. Thanks. On another note... you should never create
// models with one giant table due to the number of issues that such
// models pose. Instead, you should always build a proper star-schema.
// But it's up to you whether you want to avoid the mistakes of others
// or you want to go insane...