Forum Discussion

NikkiSavage's avatar
NikkiSavage
Icon for Helper II rankHelper II
5 years ago

Same formula in separate column not working properly

I have two columns that I have added in a table. I want to look up against customer code and determine from separate tables whether an event has happened within the last 30 days.

For the one column, I have entered the below formula and it works perfectly:

 

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, when I use the same formula in another column but filter out "type of incident", it returns the total count per place rather than per customer.
Falls in last 30 days = var today = today()
return
calculate(countrows('All Resident Incidents Raw Data'), ('All Resident Incidents Raw Data'[Type] = "fall"),
filter( all('All Resident Incidents Raw Data'[DateOfIncident].[Date]),
'All Resident Incidents Raw Data'[DateOfIncident].[Date] > today - 30 &&
'All Resident Incidents Raw Data'[DateOfIncident].[Date] <= today))
 
How do I get this to update to reflect customer code only rather than count of place.
 
I also want to do a similar column summing weight change in that date period from another table but can't get the formula to work. Any suggestions would be welcome.
 
Falls in last 30 days = var today = today()
return
calculate(countrows('All Resident Incidents Raw Data'), ('All Resident Incidents Raw Data'[Type] = "fall"),
filter( all('All Resident Incidents Raw Data'[DateOfIncident].[Date]),
'All Resident Incidents Raw Data'[DateOfIncident].[Date] > today - 30 &&
'All Resident Incidents Raw Data'[DateOfIncident].[Date] <= today))
 
Thanks!
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    Hi everyone, I'm new to power bi. Please help me separate the data from the virtual table into the corresponding tables as follows:

     

    I want to separate the data lines and from the arrow to fill the data before the arrow in the columns in the respective tables, please help me, thanks a lot