Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 "...
Need Help
  • MikeJohnsonZA's avatar
    MikeJohnsonZA
    5 years ago

    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] )
    )

     

     

MikeJohnsonZA's avatar
MikeJohnsonZA
Responsive Resident
5 years ago

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] )
)