Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create Table based on IF statement

I have a simple job table with 3 columns, job_ID(pk), date_updated, updated_by(user)

 

A job can be updated several times by both a user and the system so its common to see multiple entries for one job_ID(pk).

 

I need to create a summary table that breaks this down to give me the max date and user where the updated_by(user) <> SYSTEM.  

However,  IF this is BLANK (i.e. there are only SYSTEM updates for the job), then give me the MAX date and user where updated_by(user) == SYSTEM.

 

Some of the max dates have entries from both a user and the system (on the same day).

 

Any help would be appreciated.

 

 

Need Help
Show and Tell
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    Hi Anonymous 

     

    Please try the following:

     

    my example (TableName = SampleJob)

     

    Formula:

    SampleJob Summarized = 

SUMMARIZE(
    SampleJob,
    [JobID],
    "MaxDate",MAX(SampleJob[Date]),
    "Last Non System User",
        var __maxDate = MAX(SampleJob[Date])
        var __LastNonSystemUser =
            CALCULATE(
                LASTNONBLANK(SampleJob[Allocated],TRUE()),
                SampleJob[Date]=__maxDate && SampleJob[Allocated] <> "CIVICAAMW"
            )
        Return
        IF(ISBLANK(__LastNonSystemUser),"CIVICAAMW",__LastNonSystemUser)
)

     

    Result:

     

    I think the only question is how to handle if you have two updates ion same date. BUt this is hard to specify.

     

    Best regards
    Michael
    -----------------------------------------------------
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    Appreciate your thumbs up!
    @ me in replies or I'll lose your thread.

     

     

     

     

     

     

     

     

4 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    Anonymous Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      This is the source data that I will load into PowerBI.  User: CIVICAAMW is the System.  There are instances where CIVICAAMW is the only update on a job.  Cheers.

       

       

      • Mikelytics's avatar
        Mikelytics
        Resident Rockstar
        3 years ago

        Hi Anonymous 

         

        Please try the following:

         

        my example (TableName = SampleJob)

         

        Formula:

        SampleJob Summarized = 

SUMMARIZE(
    SampleJob,
    [JobID],
    "MaxDate",MAX(SampleJob[Date]),
    "Last Non System User",
        var __maxDate = MAX(SampleJob[Date])
        var __LastNonSystemUser =
            CALCULATE(
                LASTNONBLANK(SampleJob[Allocated],TRUE()),
                SampleJob[Date]=__maxDate && SampleJob[Allocated] <> "CIVICAAMW"
            )
        Return
        IF(ISBLANK(__LastNonSystemUser),"CIVICAAMW",__LastNonSystemUser)
)

         

        Result:

         

        I think the only question is how to handle if you have two updates ion same date. BUt this is hard to specify.

         

        Best regards
        Michael
        -----------------------------------------------------
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
        Appreciate your thumbs up!
        @ me in replies or I'll lose your thread.