One to Many Relation show unrelated rows

[Anonymous]

Hello Community,

I have two tables with a one to many relation. After creating the relationship in the data model, I created a pivot table. There is a measure to count the rows of the many side. Now I want to add a measure, to show the rows from the one side of the relation were have no relation to the second table.

I would be thankful for any advice.

Kind regards

[v-cazheng-msft]

Hi @Anonymous,

You may try this Measure.

Count PrimaryKey2 = 
VAR res =
    VALUES ( 'Table 1'[PrimaryKey] )
VAR res_ =
    CALCULATE (
        COUNT ( 'Table 2'[PrimaryKey2] ),
        FILTER ( ALL ( 'Table 2' ), 'Table 2'[ForeignKeyTable1] IN res )
    )
RETURN
    IF ( ISBLANK ( res_ ), "", 1 )

Then, the result looks like this.

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Anonymous]

Hello, the measure do not show the required result:

I want to show also columns of the many table in the pivot row filter. But the values of the many table should be blank if there is no matching data record in the many table.
Kind regards

[v-cazheng-msft]

Hi @Anonymous,

Maybe you can try this Measure.

Count PrimaryKey2 =
VAR res =
    CALCULATE (
        MAX ( 'Table 2'[PrimaryKey2] ), //If you would like to count, please replace MAX function with COUNT function
        FILTER (
            'Table 2',
            'Table 2'[ForeignKeyTable1] = MAX ( 'Table 1'[PrimaryKey] )
        )
    ) //Get matched PrimaryKey2
RETURN
    IF ( ISBLANK ( res ), "", res )

The result looks like this. Is this what you expected?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Anonymous]

Hello johnt75, thank you for your reply.

But the result show alle the missing relations, not only the one missing row with empty table2 data.

Kind regards

[johnt75]

Try

Table 1 Only =
CALCULATE (
    SUMX ( 'Table 1', IF ( ISEMPTY ( RELATEDTABLE ( 'Table 2' ) ), 1, 0 ) ),
    ALLEXCEPT ( 'Table 1', 'Table 1'[Primary Key] )
)

[Anonymous]

Hello johnt75,

the measure still do not show the wanted result. I want to show the information of table 2 as blank in this specific row, where table 1 has no relation to table 2.

Kind regards

[johnt75]

Ah, not sure if you can do that in a pivot table.

[johnt75]

You could try

Table 1 only = COUNTROWS( EXCEPT( VALUES('Table 1'[Primary Key]), VALUES('Table 2'[ForeignKeyTable1])))