cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Anonymous
Not applicable

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

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:

1 ACCEPTED SOLUTION
Solution Sage
``````// 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...``````
Solution Sage
``````// 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...``````

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors