Forum Discussion

dostdahl's avatar
dostdahl
Icon for Helper I rankHelper I
4 years ago
Solved

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 c...
  • dostdahl's avatar
    dostdahl
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

dostdahl's avatar
dostdahl
Icon for Helper I rankHelper I
4 years ago

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