Sum column for each distinct ID and include filters

[apolarwright]

Hi. Trying to write some dax for a measure. 

I have a table that I've expanded out, so the ID column has many of the same values. I want a measure where I sum up all "Working Days Taken", but only if certain criteria is met (e.g., Approval Status column is equal to 1). 

I also only want to include a value once in the sum for each ID.

I've written some Dax. But it's returning too high a number, because it's it's summing up all values, rather than once per ID.

Sum of Working Days = calculate(SUM('Holiday Requests'[Working Days Taken]), 'Holiday Requests'[Request Type] = "holiday", 'Holiday Requests'[Approval Status] <> "rejected", YEAR('Holiday Requests'[Date]) = YEAR(TODAY()))

[v-yiruan-msft · Accepted solution]

Hi @apolarwright ,

Please update the formula of measure [Sum of Working Days] as below and check if it can return the expected result... You can find the details in the attachment.

Sum of Working Days =
CALCULATE (
    SUM ( 'Holiday Requests'[Working Days Taken] ),
    FILTER (
        'Holiday Requests',
        'Holiday Requests'[Request Type] = "holiday"
            && 'Holiday Requests'[Approval Status] <> "rejected"
            && YEAR ( 'Holiday Requests'[Date] ) = YEAR ( TODAY () )
    )
)

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Renan_Barrios]

Olá, tente escrever desta forma, a expressão calculate na parte dos filtros trabalha melhor usando a formula FILTER.

Hello, try writing this way, the calculate expression in the filters part works best using the FILTER formula.

Sum of Working Days = calculate(SUM('Holiday Requests'[Working Days Taken]),Filter('Holiday Requests', 'Holiday Requests'[Request Type] = "holiday"), Filter('Holiday Requests', 'Holiday Requests'[Approval Status] <> "rejected"),Filter('Holiday Requests', YEAR('Holiday Requests'[Date]) = YEAR(TODAY())   ))