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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dostdahl
Helper I
Helper I

How 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 IDStatusDate FromPrevious StatusPrevious DateResearch CountCultivation Count
1Research3/28/2022  10
1Cultivation3/30/2022Research3/28/202201

 

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 IDStatusDate FromPrevious StatusPrevious DateResearch CountCultivation Count
1Research3/28/2022  00
1Cultivation3/30/2022Research3/28/202201

 

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

1 ACCEPTED SOLUTION

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 IDStatusDate FromPrevious StatusPrevious DateResearch CountCultivation CountNewest Status Date
1Research3/28/2022  003/30/2022
1Cultivation3/30/2022Research3/28/2022013/30/2022

 

Thanks again for your help.

 

-Dereck

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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 .

Ailsamsft_0-1651481507730.png

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.

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 IDStatusDate FromPrevious StatusPrevious DateResearch CountCultivation CountNewest Status Date
1Research3/28/2022  003/30/2022
1Cultivation3/30/2022Research3/28/2022013/30/2022

 

Thanks again for your help.

 

-Dereck

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.