Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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()returncalculate(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
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
Hi Nikki,
It would be good if you share some sample data and expected result.
Thanks
Hari
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |