Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

I have a table with columns MONTH, StartDate, EndDate, Customer, Duration, SKU, Promo.…

  1. 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

  2. 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 Table with the actual name of your table.

    This formula uses the CALCULATE function along with the SUM function to calculate the sum of the Duration column. The FILTER function is used to filter the rows based on the specified conditions.

    The ISFILTERED function checks if the MONTH and Promo columns are filtered. The COUNTROWS function with the FILTER function checks if there are more than 1 rows with the same Customer, SKU, MONTH, and Promo values.

    The EARLIER function 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.

  3. User

    I Am not sure that Isfiltered function is required

  4. Assistant

    You are correct. The ISFILTERED function is not required in this case. Here's an updated version of the DAX formula without using ISFILTERED:

    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 CALCULATE function along with the SUM function to calculate the sum of the Duration column.

    The FILTER function is used to filter the rows based on the specified conditions.

    The COUNTROWS function with the FILTER function 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 EARLIER function 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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.