Forum Discussion

subguts's avatar
subguts
Regular Visitor
3 years ago

Need help with a measure

Hi, I have a table as shown below.

 

The StatusEnd column equals different statuses, In Progress, Submitted, etc.

The RequestID is the unique ID for a request and therefore can appear multiple times.  It appears on each record as the status changes.

What I need is to be able to write a measure, or something, that will allow me to get the StatusEnd the last time each RequestID was Modified.  So I know it involves using the MAX(Modified) and StatusEnd="<status I want to count>", but having a difficutlt time figuring out the syntax so it does this for each unique RequestID.

In summary, I want to know how many RequestIDs at the time the dashboard is refreshed are in each of the different statuses.  So a Count of how many requests are now in a "In Progress", a Count of how many requests are in a "Submitted" and so forth.

 

 

Hope this makes sense, but if not please ask me questions.  Banging my head on this one for a few days now 😞

 

Thank you

Need Help

5 Replies

  • Bhu1singh's avatar
    Bhu1singh
    Frequent Visitor
    3 years ago

    Hi,

    If I understood issue correctly, you need the StatusEnd of each request where modified date is maximum.

    This can be achived in Power Query and Dax both.

    Power Query follow following steps:

    1. Right click the query and create a reference/duplicate of the query

    2. In the new Query (which is a duplicate of old one) , in home, go to Group by option 

    3. Group the query on Request id and create new column , choose operation "Max" , choose column "modified", click OK.

    4. Now go old query and used "Merge Queries" transformation and innerjoin these to table based on request ID and Modified(Date) , you should get the desired output.

    (note: joining on datetime column is inefficient better replace it with a key)

    let me know if you want dax option as well.

     

    Thanks,

    Bhuvan

    • subguts's avatar
      subguts
      Regular Visitor
      3 years ago

      Thanks Bhuvan for the quick response,  Yes I would very much like the DAX as it would be prefrerable not to create the duplicate table.  So if there is a way to do this in DAX, I'd like to try that first.

      Thank you so muchBhu1singh !

  • subguts's avatar
    subguts
    Regular Visitor
    3 years ago

    Replying to see if anyone can provide the DAX to do this as I would prefer that over a transform.  Thanks!

  • Bhu1singh's avatar
    Bhu1singh
    Frequent Visitor
    3 years ago

    Hi,

    Create a calculated column sometime like this:

    LatestStatus =
    CALCULATE (
    MAX ( <table name>[StatusEnd] ),
    ALLEXCEPT ( <table name>, <table name>[RequestID] ),
    <table name>[Modified] = <table name>[Modified]
    )

    note: we can also write a measure in same way.
     
    Thanks
    Bhuvan
    • subguts's avatar
      subguts
      Regular Visitor
      3 years ago

      Does this require 2 different tables?
      Also, if I use the same table every where it says <table name> it doesn't cause an error, but doesn't enable me to get the infomration I want, which is the the number of RequestIDs that have a certain StatusEnd.  So it's the count of all RequestIDs with let's say StatusEnd ="Submitted".  However, you have to look for the last modified record in the table since each Request can be in a different status at some point.  So again, the intent is to get the current status for each request and give a total of how many requests at the time the dashboard is diplayed are in each of the statuses that can be in the Status End field.

       

      Hope this makes sense and understand this may be difficult to understand/answer via this forum.

       

      Thank you