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 - AvailableEach 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 reasonI plan on making this my final table.Table 2 - OutageThis would be taken from Table 1 and use some logic to filter it down to only outage that pertain to that records dayThere 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 1If none of the store outages effect that day we would move onto the next tableTable 3 - AssignmentThis is a table filled with any assignments a person has for a given dayThere can be more than one record in this tableThere would need to be rules set on which would be there final valueA 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 tableIf 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 assignmentIf a person has more than one assignment, always take the one that has no assignment blankIf 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 Day12/1/20181Sick12/1/20182Hard Labor12/1/20183Hard Labor12/2/20181Sick12/2/20182RDO12/2/20183Driver12/1/20184Admin12/2/20184Admin12/1/20185No Assignment12/2/20185No Assignment Table 1 - Available Date pID12/1/2018112/1/2018212/1/2018312/2/2018112/2/2018212/2/2018312/1/2018412/2/2018412/1/2018512/2/20185 Table 2 - Outage DatePIDOutage.0.TypeOutage.0.StartOutage.0.EndOutage.1.TypeOutage.1.StartOutage.1.End12/1/20181Sick11/1/201812/2/2018RDO12/2/201812/2/201812/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 Type12/1/20182Hard Labor 12/1/20182AdminDiverted12/1/20184Admin 12/1/20183Hard Labor 12/2/20183DriverDiverted12/2/20184Hard Labor 12/2/20184Admin

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

Forum Discussion

thmonte

Helper IV

7 years ago

Solved

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

Need Help

Anonymous

7 years ago

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

Modified Sample.pbix224 KB

3 Replies

Anonymous

Not applicable

7 years ago

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

Modified Sample.pbix203 KB

thmonte

Helper IV

7 years ago

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.

Anonymous

Not applicable

7 years ago

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

Modified Sample.pbix224 KB

Forum Discussion

Complicated Relationship Tables Using Look Ups

3 Replies

Related content

Relationship with Fact tables

Relationship

Complicated IF

Complicated relationship in tables

Extracting complicated text from a column