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...
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.

     

     

     

     

     

     

     

     

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.

     

     

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hey mate,

       

      Firstly, I really appreciate the reply and the attention to detail in the answer, amazing.

       

      I have implemented the table and it works great.  The only issue left is that where there is a SYSTEM entry with a greater max(date) than a USER entry, its pulling back the system entry. (NOTE Sorry I put the system user as CIVICAAMW when it is actually CIVICAMW.  But I changed your code to reflect this).   

       

      JOBID                   Date                         Allocated

      285586                27/05/2021               STODBA

      285586                23/06/2021               CIVICAMW

       

      In this example I would need it to pull back 27/05/2021  STODBA, ignoring the real max as Allocated <> "CIVICAMW".

       

      Of course ther are entries with only system entries so in that case I just need the max.

       

      Cheers,

      JP