Compare columns from different tables in one table visual

Hi  MoOnan 

You can refer to the folloing example.

1.Create two rank table in two column tables.

Column = RANKX(FILTER(LocalColumns,[FileId]=EARLIER(LocalColumns[FileId])),[ ColumnNameLocal])

Rankx = RANKX(FILTER(SQLColumns,[ObjectId]=EARLIER(SQLColumns[ObjectId])),[ ColumnNameSQL])

2.Create a calculated table

Table =
VAR a =
    GENERATESERIES ( 1, 5, 1 )
VAR b =
    SUMMARIZE ( SQLTables, [TableNameSQL], [ObjectId] )
VAR c =
    SUMMARIZE ( LocalTables, [ TableNameLocal], [FileId] )
VAR _uion =
    UNION ( GENERATEALL ( a, b ), GENERATEALL ( a, c ) )
VAR _add1 =
    ADDCOLUMNS (
        _uion,
        "SQLColumn",
            MAXX (
                FILTER (
                    SQLColumns,
                    [ObjectId] = EARLIER ( SQLTables[ObjectId] )
                        && [Rankx] = EARLIER ( [Value] )
                ),
                [ ColumnNameSQL]
            ),
        "Localcolumn",
            MAXX (
                FILTER (
                    LocalColumns,
                    [FileId] = EARLIER ( SQLTables[ObjectId] )
                        && [Column] = EARLIER ( [Value] )
                ),
                [ ColumnNameLocal]
            )
    )
RETURN
    ADDCOLUMNS (
        _add1,
        "Final",
            SWITCH (
                TRUE (),
                COUNTROWS (
                    FILTER (
                        _add1,
                        [TableNameSQL] = EARLIER ( [TableNameSQL] )
                            && [Localcolumn] = EARLIER ( [SQLColumn] )
                    )
                ) = 1,
                    MAXX (
                        FILTER (
                            _add1,
                            [TableNameSQL] = EARLIER ( [TableNameSQL] )
                                && [Localcolumn] = EARLIER ( [SQLColumn] )
                        ),
                        [Localcolumn]
                    ),
                [Localcolumn]
                    IN SUMMARIZE (
                        FILTER (
                            _add1,
                            [TableNameSQL] = EARLIER ( SQLTables[TableNameSQL] )
                                && [SQLColumn] <> BLANK ()
                        ),
                        [SQLColumn]
                    )
                        = FALSE (), [Localcolumn]
            )
    )

Put the sqlcolumn and final column to the table visual.and create a measure to the visual filter

Measure = IF(SELECTEDVALUE('Table'[Final])=BLANK()&&SELECTEDVALUE('Table'[SQLColumn])=BLANK(),0,1)

 

Output

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.