Groups based on measure considering date slicer

[GaborBenyei]

Hello, I would like to count the number of customers depending on the number of shops they transacted in. Everything should be calculated considering a date slicer. The Summary table is the expected result. Any help is much appriaciated.
Raw data:

TransactionDate	CustomerId	ShopId	Amount
01/01/2025	Customer1	Shop1	50
01/01/2025	Customer1	Shop2	40
01/01/2025	Customer1	Shop3	60
01/01/2025	Customer2	Shop1	12
01/01/2025	Customer2	Shop5	20
01/01/2025	Customer2	Shop6	30
01/01/2025	Customer2	Shop7	25
01/01/2025	Customer3	Shop8	26
01/01/2025	Customer4	Shop2	27
02/01/2025	Customer1	Shop1	50
02/01/2025	Customer2	Shop5	20
02/01/2025	Customer3	Shop8	26
02/01/2025	Customer4	Shop2	27
02/01/2025	Customer5	Shop3	28
02/01/2025	Customer5	Shop4	29
03/01/2025	Customer1	Shop1	50
03/01/2025	Customer2	Shop5	20
03/01/2025	Customer3	Shop8	26
03/01/2025	Customer4	Shop2	27
04/01/2025	Customer1	Shop1	50
04/01/2025	Customer1	Shop2	40
04/01/2025	Customer2	Shop5	20
04/01/2025	Customer2	Shop6	20
04/01/2025	Customer3	Shop8	26
04/01/2025	Customer4	Shop2	27

Sample1:

TransactionDate: 01/01/2025-04/01/2025
CustomerId	Count of Shops
Customer1	3
Customer2	4
Customer3	1
Customer4	1
Customer5	2

Summary1:

Count of Shops	Count of Customers
1	2
2	1
3 or more	2

Sample2

TransactionDate: 02/01/2025-04/01/2025
CustomerId	Count of Shops
Customer1	2
Customer2	2
Customer3	1
Customer4	1
Customer5	2

Summary2:

Count of Shops	Count of Customers
1	2
2	3
3 or more	0

[v-ssriganesh · Accepted solution]

Hello @GaborBenyei,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I’ve reproduced your scenario using sample data matching your raw data structure and achieved the expected output for both date ranges (01/01/2025 - 04/01/2025 and 02/01/2025 - 04/01/2025). I’m attaching a .pbix file for your reference, which includes the solution implemented with your sample data. Below are the steps I followed to create it:

• I loaded your raw data into a table named Transactions in Power BI and checked TransactionDate is formatted as a Date type.
  
  
• Added the following DAX measures to the Transactions table:

ShopCountPerCustomer =

DISTINCTCOUNT(Transactions[ShopId])

CustomersWith1Shop =

CALCULATE(

    DISTINCTCOUNT(Transactions[CustomerId]),

    FILTER(

        ADDCOLUMNS(VALUES(Transactions[CustomerId]), "ShopCount", [ShopCountPerCustomer]),

        [ShopCount] = 1

    )

)

CustomersWith2Shops =

CALCULATE(

    DISTINCTCOUNT(Transactions[CustomerId]),

    FILTER(

        ADDCOLUMNS(VALUES(Transactions[CustomerId]), "ShopCount", [ShopCountPerCustomer]),

        [ShopCount] = 2

    )

)

CustomersWith3OrMoreShops =

CALCULATE(

    DISTINCTCOUNT(Transactions[CustomerId]),

    FILTER(

        ADDCOLUMNS(VALUES(Transactions[CustomerId]), "ShopCount", [ShopCountPerCustomer]),

        [ShopCount] >= 3

    )

)

CustomerCountByShopGroup =

SWITCH(

    SELECTEDVALUE(SummaryCategories[Count of Shops]),

    "1", [CustomersWith1Shop],

    "2", [CustomersWith2Shops],

    "3 or more", [CustomersWith3OrMoreShops],

    BLANK()

)

• Created a disconnected table named SummaryCategories using Enter Data with one column Count of Shops and these values:

• "1"
• "2"
• "3 or more"

• Added a Slicer using Transactions[TransactionDate] set to “Between” mode.
• Added a Table visual with CustomerId and ShopCountPerCustomer to verify per-customer shop counts.
• Added a Table visual with SummaryCategories[Count of Shops] and CustomerCountByShopGroup for the summary output.
  
  

The .pbix file attached demonstrates this working solution with your sample data. You can adjust the slicer to test different date ranges, and the measures will dynamically update to reflect the number of customers grouped by shop counts.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[GaborBenyei]

Hi @v-ssriganesh , Thank you for your solution, it works as expected. The only issue is when I try to use this  in my original database, that contains millions of rows, it takes ages to calculate and got this error message if the selected period is longer. Any idea for this? Maybe a different approach? I used calculated table for a similar task where I did not needed to use date slicers, and that performed better. But unfortunatelly calculated tables does not consider the selected period dinamically. Aything similar to that?

[rajendraongole1]

Hi @GaborBenyei  - you can create a measure to count the distinct number of shops each customer transacted in within the selected date range

CountShops =
CALCULATE(
DISTINCTCOUNT(Transactions[ShopId]),
ALLEXCEPT(Transactions, Transactions[CustomerId])
)

Create a Measure for Summary Table

CustomerCountByShopGroup =
VAR SummaryTable =
ADDCOLUMNS(
SUMMARIZE(Transactions, Transactions[CustomerId]),
"ShopCount", [CountShops]
)
VAR Count1 = COUNTROWS(FILTER(SummaryTable, [ShopCount] = 1))
VAR Count2 = COUNTROWS(FILTER(SummaryTable, [ShopCount] = 2))
VAR Count3OrMore = COUNTROWS(FILTER(SummaryTable, [ShopCount] >= 3))

RETURN
UNION(
ROW("Count of Shops", "1", "Count of Customers", Count1),
ROW("Count of Shops", "2", "Count of Customers", Count2),
ROW("Count of Shops", "3 or more", "Count of Customers", Count3OrMore)
)

Now, you can use CustomerId and CountShops in a table to see individual customer counts.

Use CustomerCountByShopGroup in another table to display the summarized count of customers by shop count categories.

[GaborBenyei]

Hi @rajendraongole1 , thanks for trying to help, however I got this error message:

[rajendraongole1]

Hi @GaborBenyei - can you please try the modified one.

CustomerCountByShopGroup =
VAR SummaryTable =
ADDCOLUMNS(
SUMMARIZE(Transactions, Transactions[CustomerId]),
"ShopCount", DISTINCTCOUNT(Transactions[ShopId])
)

VAR Count1 = COUNTROWS(FILTER(SummaryTable, [ShopCount] = 1))
VAR Count2 = COUNTROWS(FILTER(SummaryTable, [ShopCount] = 2))
VAR Count3OrMore = COUNTROWS(FILTER(SummaryTable, [ShopCount] >= 3))

RETURN
UNION(
ROW("Count of Shops", "1", "Count of Customers", Count1),
ROW("Count of Shops", "2", "Count of Customers", Count2),
ROW("Count of Shops", "3 or more", "Count of Customers", Count3OrMore)
)

Hope this helps

[GaborBenyei]

Hi @rajendraongole1 , Unfortunately I got the same error message.