DAX measure producing duplicates

[brian0782]

Hi

I've written the below DAX and included ALL to ignore any page level filters

Total Cases = CALCULATE(COUNTX('Case Record','Case Record'[Case ID]), FILTER(ALL('Case Record'),'Case Record'[Case Type ID]=1))

However when I drop the measure into a visual I'm getting repeating values. I'm confident my relationships have been configured properly. 

Any ideas why it's not working properly? Any help much appreciated.

[selimovd · Accepted solution]

Hey @brian0782 ,

is the column "Week" also from the table 'Case Record'?

If yes, with the ALL you also remove the filter of Week, so every time you get the total.

Maybe if that's the case, the following approach could work:

Total Cases =
CALCULATE(
    COUNT( 'Case Record'[Case ID] ),
    ALLEXCEPT(
        'Case Record',
        'Case Record'[Week]
    ),
    'Case Record'[Case Type ID] = 1
)

It would be helpful to get more information or even better the file in order to help you.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[brian0782]

This worked well. Thanks for the explanation 😁