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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

 

1 ACCEPTED SOLUTION

Hi @Anonymous 

 

Please try the following:

 

my example (TableName = SampleJob)

Mikelytics_0-1673964154782.png

 

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:

Mikelytics_1-1673964231740.png

 

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.

 

 

 

 

 

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

4 REPLIES 4
Mahesh0016
Super User
Super User

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

Anonymous
Not applicable

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.

 

jp_wheatley_0-1673958108118.png

 

Hi @Anonymous 

 

Please try the following:

 

my example (TableName = SampleJob)

Mikelytics_0-1673964154782.png

 

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:

Mikelytics_1-1673964231740.png

 

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.

 

 

 

 

 

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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