cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jack421
Helper IV
Helper IV

create table based on another table

Hi all, I currently have a table (Table 1) that I want to use to create Table 2. For Table 2, I would like only one row for each customer ID, a column indicating whether the customer has the string "premium plus" in any of its SKUs, and the Max Quantity for that customer. Please see the example tables below, I would like to know how to use the summarize function or other functions to create Table 2. Thanks!

 

Table 1

Customer IDSKUQuantity
1Premium Plus X5
1Regular2
2Regular2
2Regular2
3Premium Plus Y5
3Regular4

 

Table 2

Customer IDHas text "Premium Plus" in any SKU?Max (Quantity)
1Yes5
2No2
3Yes5
1 ACCEPTED SOLUTION

Hi

 

To create a table ased on a single row per unique value we can use the SUMMARIZECOLUMNS funtion, we then nee to calculate the two values, this is faiully striagforward for the second on as it is simply the Maximum value for each row. The 'premium plus' is a little harder but can be done using the SEARCH function to finter for SKU's with the condition then counting such rows then converting those into YES or No. The below formula shoudl work for you to create the table you are looking for. 

 

Table2 =
SUMMARIZECOLUMNS (
    'Table1'[Customer ID],
    "Has text 'Premium Plus' in any SKU?",
        IF (
            COUNTROWS (
                FILTER ( 'Table1', SEARCH ( "Premium Plus", Table1[SKU], 1, 0 ) > 0 )
            ),
            "Yes",
            "No"
        ),
    "Max (Quantity)", MAX ( 'Table1'[Quantity] )
)

 

 

View solution in original post

1 REPLY 1

Hi

 

To create a table ased on a single row per unique value we can use the SUMMARIZECOLUMNS funtion, we then nee to calculate the two values, this is faiully striagforward for the second on as it is simply the Maximum value for each row. The 'premium plus' is a little harder but can be done using the SEARCH function to finter for SKU's with the condition then counting such rows then converting those into YES or No. The below formula shoudl work for you to create the table you are looking for. 

 

Table2 =
SUMMARIZECOLUMNS (
    'Table1'[Customer ID],
    "Has text 'Premium Plus' in any SKU?",
        IF (
            COUNTROWS (
                FILTER ( 'Table1', SEARCH ( "Premium Plus", Table1[SKU], 1, 0 ) > 0 )
            ),
            "Yes",
            "No"
        ),
    "Max (Quantity)", MAX ( 'Table1'[Quantity] )
)

 

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors