Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NikkiSavage
Helper II
Helper II

all except and filter of days is skewing my data

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
 
3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
NikkiSavage
Helper II
Helper II

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
Solution Sage
Solution Sage

Hi Nikki,

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

Thanks

Hari

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.