Forum Discussion

yelgrass's avatar
yelgrass
New Member
3 years ago
Solved

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...
  • barritown's avatar
    barritown
    3 years ago

    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

     

     

barritown's avatar
barritown
Icon for Solution Sage rankSolution Sage
3 years ago

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