Forum Discussion
Helper IHow to get a correct count when statuses change
Hello,
I'm running into an issue achieving a correct status count for a unique account number. Here is the scenario:
We are tracking the status that an account is currently in. The statuses can be updated and the new status dates are tracked as well. The 4 statuses are Research, Cultivation, Solicitation, and Stewardship. For whatever the current status that an account is in, I've created 4 calculated columns that I use to sum up for a total.
The calcuated column DAX is currently:
Research Count= IF(Table[Status] = "Research", 1, 0).
This is used for each status and then I tally up the 1's to get a count. It works fine until an account changes a status, which looks something like this:
| Unique ID | Status | Date From | Previous Status | Previous Date | Research Count | Cultivation Count |
| 1 | Research | 3/28/2022 | 1 | 0 | ||
| 1 | Cultivation | 3/30/2022 | Research | 3/28/2022 | 0 | 1 |
The new current status for the unique ID has a 1 in the column, but the Research count also keeps the 1 which is throwing off my actual count.
My desired outcome would be able to account for these status changes and only show a 1 in the status column for the current status and show a 0 for the previous/other statuses for each unique ID.
| Unique ID | Status | Date From | Previous Status | Previous Date | Research Count | Cultivation Count |
| 1 | Research | 3/28/2022 | 0 | 0 | ||
| 1 | Cultivation | 3/30/2022 | Research | 3/28/2022 | 0 | 1 |
I've tried a few differnet ways in DAX, but am still learning. Thanks in advance for any advice, let me know if I can provide any additional details!
-Dereck
Hi Anonymous - Thank you for your response. After looking at it some more, I was able to find a workable solution. As you stated above, the calculated columns were returning a 1 for what ever Status was in the Status column, for each Unique ID. But when the status changes, I needed to have the old status show a 0, with the new status showing 1.
The solution I found was to create a calculated column to show the most recent date for a status:
Newest Status Date =MAXX (FILTER (ALL ( Table ),Table[Unique ID] = EARLIER ( Table[Unique ID] )),Table[Date From])And then adjusted the Research Count and Cultivation Count column DAX to:Research Count =IF (Table[Status] = "Research"&& Table[Date From] = Table[Newest Status Date],1,0)This allows the table to show 1 current status count when there is a change.Unique ID Status Date From Previous Status Previous Date Research Count Cultivation Count Newest Status Date 1 Research 3/28/2022 0 0 3/30/2022 1 Cultivation 3/30/2022 Research 3/28/2022 0 1 3/30/2022 Thanks again for your help.
-Dereck
2 Replies
Hi dostdahl
From your DAX you can see , if Status = "Research" then return 1 , so it return 1 in Research Count . From the data and formulas you have provided so far, there is nothing wrong with this .
Could you provide your pbix file or more detailed data or formulas to illustrate your problem ?
Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dostdahlHi Anonymous - Thank you for your response. After looking at it some more, I was able to find a workable solution. As you stated above, the calculated columns were returning a 1 for what ever Status was in the Status column, for each Unique ID. But when the status changes, I needed to have the old status show a 0, with the new status showing 1.
The solution I found was to create a calculated column to show the most recent date for a status:
Newest Status Date =MAXX (FILTER (ALL ( Table ),Table[Unique ID] = EARLIER ( Table[Unique ID] )),Table[Date From])And then adjusted the Research Count and Cultivation Count column DAX to:Research Count =IF (Table[Status] = "Research"&& Table[Date From] = Table[Newest Status Date],1,0)This allows the table to show 1 current status count when there is a change.Unique ID Status Date From Previous Status Previous Date Research Count Cultivation Count Newest Status Date 1 Research 3/28/2022 0 0 3/30/2022 1 Cultivation 3/30/2022 Research 3/28/2022 0 1 3/30/2022 Thanks again for your help.
-Dereck
