Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ID | SKU | Quantity |
1 | Premium Plus X | 5 |
1 | Regular | 2 |
2 | Regular | 2 |
2 | Regular | 2 |
3 | Premium Plus Y | 5 |
3 | Regular | 4 |
Table 2
Customer ID | Has text "Premium Plus" in any SKU? | Max (Quantity) |
1 | Yes | 5 |
2 | No | 2 |
3 | Yes | 5 |
Solved! Go to 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] )
)
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] )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
150 | |
103 | |
103 | |
88 | |
66 |