Forum Discussion
Aggregation Logic
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!
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
1 Reply
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