Aggregation Logic

[yelgrass]

Hi all,

I have a table with primary keys CustomerID, YearMonth, and Product. This table will be filtered by a YearMonth slicer, and I want to create a measure that groups each CustomerID into one of 4 categories and then calculates DistinctCount(CustomerID) within each category to be displayed on a donut chart.

Categories

"A&B", "B&C", "A&C", "All 3"

Sample data

CustomerID	YearMonth	Product	Flag
5001	2022-01	B	Y
5001	2022-02	A	Y
5001	2022-02	C	Y
5001	2022-03	A	Y
5001	2022-03	C	Y
5002	2022-02	B	Y
5002	2022-03	B	Y
5002	2022-03	C	Y
5002	2022-04	B	N
5002	2022-04	C	Y

Scenarios

1) If 2022-01 through 2022-04 is selected, then Customer 5001 should be counted as "All 3", because each Product shows up at least once for this CustomerID within the time period.  Customer 5002 should be counted as "B&C".

2) If 2022-03 through 2022-04 is selected, then Customer 5001 should be counted as "A&C". Customer 5002 should be counted as "B&C"

3) If only 2022-01 is selected, neither customer should be counted in any of the 4 groups. Customer 5001 only has Product B, and Customer 5002 has none.

4) If only 2022-04 is selected, neither customer should be counted in any of the 4 groups. Customer 5001 has none, and Customer 5002 only has Product C. (We only want to consider records where Flag = Y).

Any ideas for a DAX query that can accomplish this? Thanks in advance!

[barritown · Accepted solution]

Hi @yelgrass,

I'm not quite sure that my approach is optimal, but it seems to work.

The idea is to create such a measure and use it for aggregation:

In plain text for convenience:

Measure = 
VAR CurrentID = MIN ( [CustomerID] )
VAR CurrentProducts = DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Dataset', 'Dataset'[CustomerID] = CurrentID && 'Dataset'[Flag] = "Y" ), "Product", [Product] ) )
RETURN 
SWITCH ( TRUE (),
         CONTAINS ( CurrentProducts, [Product], "A" ) && CONTAINS ( CurrentProducts, [Product], "B" ) && CONTAINS ( CurrentProducts, [Product], "C" ), "All 3",
         CONTAINS ( CurrentProducts, [Product], "A" ) && CONTAINS ( CurrentProducts, [Product], "B" ), "A&B",
         CONTAINS ( CurrentProducts, [Product], "B" ) && CONTAINS ( CurrentProducts, [Product], "C" ), "B&C",
         CONTAINS ( CurrentProducts, [Product], "A" ) && CONTAINS ( CurrentProducts, [Product], "C" ), "A&C",
         BLANK() )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian