all except and filter of days is skewing my data

[NikkiSavage]

Hi.

I am trying to do a dax in power bi to return the total number of infections that a patient has had in the last 2 weeks. For some reason, it is not working properly. As an example, when I post the below formula, I get the total number of infections and the column correctly returns a 1 for every infection within the time period of 30 days.

Infections in last 30 days = var today = today()

return

calculate(countrows('Infections Raw Data'),

filter( all('Infections Raw Data'[CommencementDate]),

'Infections Raw Data'[CommencementDate] > today - 30 &&

'Infections Raw Data'[CommencementDate] <= today))

However, I want to group by patient. As such, I add in an 'all except' as noted below:

Infections in last 30 days = var today = today()

return

calculate(countrows('Infections Raw Data'),

allexcept('Infections Raw Data', 'Infections Raw Data'[Customer_Code]),

filter( all('Infections Raw Data'[CommencementDate]),

'Infections Raw Data'[CommencementDate] > today - 30 &&

'Infections Raw Data'[CommencementDate] <= today))

This still gives me the correct result.

However, when I change the dax to be 14 days, I get some random extra lines in my data with a "1" being returned for an infection in January for example. DAX below:

Infections in last 14 days = var today = today()

return

calculate(countrows('Infections Raw Data'),

allexcept('Infections Raw Data', 'Infections Raw Data'[Customer_Code]),

filter( all('Infections Raw Data'[CommencementDate]),

'Infections Raw Data'[CommencementDate] > today - 14 &&

'Infections Raw Data'[CommencementDate] <= today))

Why would just updating the days from 30 to 14 throw out my dax and not return the correct result?

Happy to take any alternative suggestions to sum the total number of infections per customer from the past 14 days.

Thanks

Nikki

[v-yiruan-msft]

Hi @NikkiSavage ,

Could you please provide the related screenshot of the displayed visual (mask the sensitive data) when change from 30 to 14 in order to make troubleshooting? Please highlight those extra rows with "1"...

---

However, when I change the dax to be 14 days, I get some random extra lines in my data with a "1" being returned for an infection in January for example. DAX below:

Infections in last 14 days = var today = today()

return

calculate(countrows('Infections Raw Data'),

allexcept('Infections Raw Data', 'Infections Raw Data'[Customer_Code]),

filter( all('Infections Raw Data'[CommencementDate]),

'Infections Raw Data'[CommencementDate] > today - 14 &&

'Infections Raw Data'[CommencementDate] <= today))

---

In addition, please update the formula of measure [Infections in last 14 days] as below and check whether you can get the correct result.

Infections in last 14 days =
VAR today =
    TODAY ()
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Infections Raw Data'[Customer_Code] ),
        FILTER (
            'Infections Raw Data'[CommencementDate],
            'Infections Raw Data'[CommencementDate] > today - 14
                && 'Infections Raw Data'[CommencementDate] <= today
        )
    )

Best Regards

[NikkiSavage]

Hi. The data is confidential and I've signed an NDA so can't share. If I try and share sample data in tables, it doesn't work. It says it is in HTML format and can't post.

thanks. Nikki

[Hariharan_R]

Hi Nikki,

It would be good if you share some sample data and expected result. 

Thanks

Hari