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
thmonte
Helper IV
Helper IV

Complicated Relationship Tables Using Look Ups

So my current application uses MongoDB which isn't natively supported by PowerBI but we usually expose the data via BI Connector and run all our reports by connecting thru the ODBC driver.

 

I've been tasked with creating a report for what each employees value is for each day they are employeed.  This data would be a mash up of 2-3 different tables.

 

Table 1 - Available

  • Each Day at 12AM a record is inserted for each active employee.
  • This record contains details about the employee including anytime they were out of work for whatever reason
  • I plan on making this my final table.

Table 2 - Outage

  • This would be taken from Table 1 and use some logic to filter it down to only outage that pertain to that records day
  • There can be multiple outages on any given person for any day and I'd like to create a heirarchy of what their final value should be if they have more than 1
  • If none of the store outages effect that day we would move onto the next table

Table 3 - Assignment

  • This is a table filled with any assignments a person has for a given day
  • There can be more than one record in this table
  • There would need to be rules set on which would be there final value
  • A person can have a value in this table and the outage table for the same day.  There final value here would always override the Outage table

 

Here's an idea of what I picture my final table would consist of based on the 2-3 Tables listed above.

  • If a person has 1+ Outage Records and No Assignment Record their value is from the Outage table
  • If a person has 1 or more Outage Records, a ranking order must be added on which over-rides which (ex Sick would always be the value if they have a Sick + RDO)
  • If a person has an assignment + outage for the matching day, always take the assignment
  • If a person has more than one assignment, always take the one that has no assignment blank
  • If a person has more than one assignment and all are blank, then take the assignment based on a specific ranking order.
  • If a person has assignments in the table and none have a blank assignment type then take assignment based on specific ranking order.
  • If no values are found in either table, mark has "No Assignment"

 

I'm hoping I'm getting this all out in words properly.  I created a PBIX with sample data for you to view

 

https://drive.google.com/open?id=1RgnpOYuZPyxafSJu0Kmh6i6EZZzj-UEp

 

 

Final Table Idea

 

Date                 pID           Value For Day

12/1/20181Sick
12/1/20182Hard Labor
12/1/20183Hard Labor
12/2/20181Sick
12/2/20182RDO
12/2/20183Driver
12/1/20184Admin
12/2/20184Admin
12/1/20185No Assignment
12/2/20185No Assignment

 

Table 1 - Available

 

   Date              pID

12/1/20181
12/1/20182
12/1/20183
12/2/20181
12/2/20182
12/2/20183
12/1/20184
12/2/20184
12/1/20185
12/2/20185

 

Table 2 - Outage

 

DatePIDOutage.0.TypeOutage.0.StartOutage.0.EndOutage.1.TypeOutage.1.StartOutage.1.End

12/1/20181Sick11/1/201812/2/2018RDO12/2/201812/2/2018
12/1/20182      
12/1/20183RDO12/1/201812/1/2018   
12/2/20181Sick11/1/201812/2/2018   
12/2/20182RDO12/2/201812/2/2018   
12/2/20183      
12/1/20184      
12/1/20185      
12/2/20184      
12/2/20185      

 

Table 3 - Assignments

 

DatePIDType Of WorkAssignment Type

12/1/20182Hard Labor 
12/1/20182AdminDiverted
12/1/20184Admin 
12/1/20183Hard Labor 
12/2/20183DriverDiverted
12/2/20184Hard Labor 
12/2/20184Admin 
1 ACCEPTED SOLUTION

Hi @thmonte,

 

Yes, I need that table to return correct one when one user has multiple assignment or outage records.

 

I add two rank tables with type ranking:

10.PNG11.PNG

 

Formula:

Desc Based on Rank = 
VAR assigList =
    CALCULATETABLE (
        VALUES ( Assignment[PID] ),
        FILTER ( ALL ( Assignment ), [Date] = EARLIER ( Summary[Date] ) )
    )
VAR outList =
    CALCULATETABLE (
        VALUES ( 'Outage Unpivoted'[PID] ),
        FILTER ( ALL ( 'Outage Unpivoted' ), [Date] = EARLIER ( Summary[Date] ) )
    )
VAR outRecord =
    COUNTROWS (
        FILTER (
            'Outage Unpivoted',
            [PID] = EARLIER ( Summary[PID] )
                && [Date] = EARLIER ( Summary[Date] )
        )
    )
VAR assigRecord =
    COUNTROWS (
        FILTER (
            'Assignment',
            [PID] = EARLIER ( Summary[PID] )
                && [Date] = EARLIER ( Summary[Date] )
        )
    )
VAR assiglistMatch =
    CALCULATETABLE (
        VALUES ( Assignment[Type Of Work] ),
        FILTER (
            'Assignment',
            'Assignment'[PID] = EARLIER ( Summary[PID] )
                && 'Assignment'[Date] = EARLIER ( Summary[Date] )
        )
    )
VAR outlistMatch =
    CALCULATETABLE (
        VALUES ( 'Outage Unpivoted'[Outage Type] ),
        FILTER (
            'Outage Unpivoted',
            'Outage Unpivoted'[PID] = EARLIER ( Summary[PID] )
                && 'Outage Unpivoted'[Date] = EARLIER ( Summary[Date] )
        )
    )
RETURN
    IF (
        NOT ( [PID] IN UNION ( assigList, outList ) ),
        "Not Assignment",
        IF (
            assigRecord > 0,
            IF (
                assigRecord >= 2,
                LOOKUPVALUE (
                    'Assignment Rank'[Assignment Type],
                    'Assignment Rank'[Rank], MINX (
                        FILTER (
                            'Assignment Rank',
                            'Assignment Rank'[Assignment Type] IN assiglistMatch
                        ),
                        [Rank]
                    )
                ),
                FIRSTNONBLANK ( assiglistMatch, [Type Of Work] )
            ),
            IF (
                outRecord > 0,
                IF (
                    outRecord >= 2,
                    LOOKUPVALUE (
                        'Outage Rank'[Outage Type],
                        'Outage Rank'[Rank], MINX (
                            FILTER ( 'Outage Rank', 'Outage Rank'[Outage Type] IN outlistMatch ),
                            [Rank]
                        )
                    ),
                    FIRSTNONBLANK ( outlistMatch, [Outage Type] )
                )
            )
        )
    )

Result:

12.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @thmonte,

 

I create a summary table based on available table and add a calculated column to stored lookup result from other tables.

Desc = 
VAR assigList =
    CALCULATETABLE (
        VALUES ( Assignment[PID] ),
        FILTER ( ALL ( Assignment ), [Date] = EARLIER ( Summary[Date] ) )
    )
VAR outList =
    CALCULATETABLE (
        VALUES ( 'Outage Unpivoted'[PID] ),
        FILTER ( ALL ( 'Outage Unpivoted' ), [Date] = EARLIER ( Summary[Date] ) )
    )
VAR outRecord =
    COUNTROWS (
        FILTER (
            'Outage Unpivoted',
            [PID] = EARLIER ( Summary[PID] )
                && [Date] = EARLIER ( Summary[Date] )
        )
    )
VAR assigRecord =
    COUNTROWS (
        FILTER (
            'Assignment',
            [PID] = EARLIER ( Summary[PID] )
                && [Date] = EARLIER ( Summary[Date] )
        )
    )
VAR assiglistMatch =
    CALCULATETABLE (
        VALUES ( Assignment[Type Of Work] ),
        FILTER (
            'Assignment',
            'Assignment'[PID] = EARLIER ( Summary[PID] )
                && 'Assignment'[Date] = EARLIER ( Summary[Date] )
        )
    )
VAR outlistMatch =
    CALCULATETABLE (
        VALUES ( 'Outage Unpivoted'[Outage Type] ),
        FILTER (
            'Outage Unpivoted',
            'Outage Unpivoted'[PID] = EARLIER ( Summary[PID] )
                && 'Outage Unpivoted'[Date] = EARLIER ( Summary[Date] )
        )
    )
RETURN
    IF (
        NOT ( [PID] IN UNION ( assigList, outList ) ),
        "Not Assignment",
        IF (
            assigRecord > 0,
            IF (
                "Admin" IN assiglistMatch,
                "Admin",
                CONCATENATEX ( assiglistMatch, [Type Of Work], "," )
            ),
            IF (
                outRecord > 0,
                IF (
                    "Sick" IN outlistMatch,
                    "Sick",
                    CONCATENATEX ( outlistMatch, [Outage Type], "," )
                )
            )
        )
    )

4.PNG

 

In add, I'm still not so clear for the ranking order of different types, can you please provide more about this?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

This looks like a great start but the ranking order I'd like to create is what value to show in the Desc column if multiple values existing in any of the other columns.

 

For example:

 

If there is 3 records for one person in the Outage table with the following Outage Types:

  • Sick
  • RDO
  • Vacation

 

then I would always want Sick to show as the value.  So maybe a seperate reference sheet with all possible Outage Types and Work Types simlar to this

Outage TypeRank
Sick1
Vacation2
RDO3
Jury Duty4

 

 

Then if the person has multiple records in either table it can reference this table and always take the lowest ranked value

 

so if someone has 2 records Jury Duty and Vacation - It will display Vacation since it is lower rank.

 

 

Hi @thmonte,

 

Yes, I need that table to return correct one when one user has multiple assignment or outage records.

 

I add two rank tables with type ranking:

10.PNG11.PNG

 

Formula:

Desc Based on Rank = 
VAR assigList =
    CALCULATETABLE (
        VALUES ( Assignment[PID] ),
        FILTER ( ALL ( Assignment ), [Date] = EARLIER ( Summary[Date] ) )
    )
VAR outList =
    CALCULATETABLE (
        VALUES ( 'Outage Unpivoted'[PID] ),
        FILTER ( ALL ( 'Outage Unpivoted' ), [Date] = EARLIER ( Summary[Date] ) )
    )
VAR outRecord =
    COUNTROWS (
        FILTER (
            'Outage Unpivoted',
            [PID] = EARLIER ( Summary[PID] )
                && [Date] = EARLIER ( Summary[Date] )
        )
    )
VAR assigRecord =
    COUNTROWS (
        FILTER (
            'Assignment',
            [PID] = EARLIER ( Summary[PID] )
                && [Date] = EARLIER ( Summary[Date] )
        )
    )
VAR assiglistMatch =
    CALCULATETABLE (
        VALUES ( Assignment[Type Of Work] ),
        FILTER (
            'Assignment',
            'Assignment'[PID] = EARLIER ( Summary[PID] )
                && 'Assignment'[Date] = EARLIER ( Summary[Date] )
        )
    )
VAR outlistMatch =
    CALCULATETABLE (
        VALUES ( 'Outage Unpivoted'[Outage Type] ),
        FILTER (
            'Outage Unpivoted',
            'Outage Unpivoted'[PID] = EARLIER ( Summary[PID] )
                && 'Outage Unpivoted'[Date] = EARLIER ( Summary[Date] )
        )
    )
RETURN
    IF (
        NOT ( [PID] IN UNION ( assigList, outList ) ),
        "Not Assignment",
        IF (
            assigRecord > 0,
            IF (
                assigRecord >= 2,
                LOOKUPVALUE (
                    'Assignment Rank'[Assignment Type],
                    'Assignment Rank'[Rank], MINX (
                        FILTER (
                            'Assignment Rank',
                            'Assignment Rank'[Assignment Type] IN assiglistMatch
                        ),
                        [Rank]
                    )
                ),
                FIRSTNONBLANK ( assiglistMatch, [Type Of Work] )
            ),
            IF (
                outRecord > 0,
                IF (
                    outRecord >= 2,
                    LOOKUPVALUE (
                        'Outage Rank'[Outage Type],
                        'Outage Rank'[Rank], MINX (
                            FILTER ( 'Outage Rank', 'Outage Rank'[Outage Type] IN outlistMatch ),
                            [Rank]
                        )
                    ),
                    FIRSTNONBLANK ( outlistMatch, [Outage Type] )
                )
            )
        )
    )

Result:

12.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.