I have a table with columns MONTH, StartDate, EndDate, Customer, Duration, SKU, Promo.…
- User
I have a table with columns MONTH, StartDate, EndDate, Customer, Duration, SKU, Promo. How to find the sum of Duration for unique combination of Customer and SKU excluding repeated rows for Customer, SKU, MONTH, and Promo. Use DAX
- Assistant
To find the sum of Duration for the unique combination of Customer and SKU, excluding repeated rows for Customer, SKU, MONTH, and Promo, you can use the following DAX formula:
SumOfDuration = CALCULATE( SUM(Table[Duration]), FILTER( Table, NOT ( ISFILTERED(Table[MONTH]) && ISFILTERED(Table[Promo]) && COUNTROWS( FILTER( Table, EARLIER(Table[Customer]) = Table[Customer] && EARLIER(Table[SKU]) = Table[SKU] && EARLIER(Table[MONTH]) = Table[MONTH] && EARLIER(Table[Promo]) = Table[Promo] ) ) > 1 ) ) )Replace
Tablewith the actual name of your table.This formula uses the
CALCULATEfunction along with theSUMfunction to calculate the sum of the Duration column. TheFILTERfunction is used to filter the rows based on the specified conditions.The
ISFILTEREDfunction checks if the MONTH and Promo columns are filtered. TheCOUNTROWSfunction with theFILTERfunction checks if there are more than 1 rows with the same Customer, SKU, MONTH, and Promo values.The
EARLIERfunction is used to reference the earlier row values while filtering.The result will be the sum of Duration for the unique combination of Customer and SKU, excluding any repeated rows for Customer, SKU, MONTH, and Promo.
- User
I Am not sure that Isfiltered function is required
- Assistant
You are correct. The
ISFILTEREDfunction is not required in this case. Here's an updated version of the DAX formula without usingISFILTERED:SumOfDuration = CALCULATE( SUM(Table[Duration]), FILTER( Table, COUNTROWS( FILTER( Table, EARLIER(Table[Customer]) = Table[Customer] && EARLIER(Table[SKU]) = Table[SKU] && EARLIER(Table[MONTH]) = Table[MONTH] && EARLIER(Table[Promo]) = Table[Promo] ) ) = 1 ) )This formula still uses the
CALCULATEfunction along with theSUMfunction to calculate the sum of the Duration column.The
FILTERfunction is used to filter the rows based on the specified conditions.The
COUNTROWSfunction with theFILTERfunction is used to count the number of rows with the same Customer, SKU, MONTH, and Promo values. If the count is equal to 1, it means the current row is unique, and the sum of its Duration is considered in the calculation.The
EARLIERfunction is used to reference the earlier row values while filtering.This updated formula will give you the sum of Duration for the unique combination of Customer and SKU, excluding any repeated rows for Customer, SKU, MONTH, and Promo.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.