Forum Discussion

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

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

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

Related content

Relationship with Fact tables

Relationship

Complicated IF

Complicated relationship in tables

Extracting complicated text from a column