One to One relationship - Filtering out unique values

[JamesLow]

I have 2 tables with unique values and I want to link the 2 tables. 1 table has more rows in than the other therefore when I link the 2 tables together in a one to one relationship, the distinct count of unique values decreases to the amount of unique values across both tables and excludes the values in the larger table that don't have a match in the other table. I want the distinct count to be to total amount of distinct values regardless if there is a match with the other table.

I need the relationship between the 2 tables as there is data in the smaller table for the unique values that do have a match.

[v-gizhi-msft · Accepted solution]

Hi,

Please try this measure:

Measure = 
COUNTROWS (
    DISTINCT (
        UNION (
            SELECTCOLUMNS ( 'Table 1', "value", 'Table 1'[Value] ),
            SELECTCOLUMNS ( 'Table 2', "value", 'Table 2'[Value] )
        )
    )
)

The result shows the two tables' total distinct count value:

See my attacted pbix file.

Best Regards,

Giotto