Complicated Relationship Tables Using Look Ups

[thmonte]

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/2018	1	Sick
12/1/2018	2	Hard Labor
12/1/2018	3	Hard Labor
12/2/2018	1	Sick
12/2/2018	2	RDO
12/2/2018	3	Driver
12/1/2018	4	Admin
12/2/2018	4	Admin
12/1/2018	5	No Assignment
12/2/2018	5	No Assignment

Table 1 - Available

   Date              pID

12/1/2018	1
12/1/2018	2
12/1/2018	3
12/2/2018	1
12/2/2018	2
12/2/2018	3
12/1/2018	4
12/2/2018	4
12/1/2018	5
12/2/2018	5

Table 2 - Outage

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

12/1/2018	1	Sick	11/1/2018	12/2/2018	RDO	12/2/2018	12/2/2018
12/1/2018	2
12/1/2018	3	RDO	12/1/2018	12/1/2018
12/2/2018	1	Sick	11/1/2018	12/2/2018
12/2/2018	2	RDO	12/2/2018	12/2/2018
12/2/2018	3
12/1/2018	4
12/1/2018	5
12/2/2018	4
12/2/2018	5

Table 3 - Assignments

DatePIDType Of WorkAssignment Type

12/1/2018	2	Hard Labor
12/1/2018	2	Admin	Diverted
12/1/2018	4	Admin
12/1/2018	3	Hard Labor
12/2/2018	3	Driver	Diverted
12/2/2018	4	Hard Labor
12/2/2018	4	Admin

[v-shex-msft]

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], "," )
                )
            )
        )
    )

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

[thmonte]

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 Type	Rank
Sick	1
Vacation	2
RDO	3
Jury Duty	4

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.

[v-shex-msft · 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:

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:

Regards,

Xiaoxin Sheng