Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
yelgrass
New Member

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

CustomerIDYearMonthProductFlag
50012022-01BY
50012022-02AY
50012022-02CY
50012022-03AY
50012022-03CY
50022022-02BY
50022022-03BY
50022022-03CY
50022022-04BN
50022022-04CY

 

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!

1 ACCEPTED SOLUTION
barritown
Super User
Super User

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:

barritown_1-1690451952047.png

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

 

 

View solution in original post

1 REPLY 1
barritown
Super User
Super User

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:

barritown_1-1690451952047.png

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

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.