Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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
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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |