topic Re: One to Many Relation show unrelated rows in DAX Commands and Tips

One to Many Relation show unrelated rows

Anonymous — Tue, 31 May 2022 11:42:29 GMT

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

Re: One to Many Relation show unrelated rows

johnt75 — Tue, 31 May 2022 12:21:45 GMT

You could try

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

Re: One to Many Relation show unrelated rows

Anonymous — Tue, 31 May 2022 12:42:49 GMT

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

Re: One to Many Relation show unrelated rows

johnt75 — Tue, 31 May 2022 12:58:19 GMT

Try

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

Re: One to Many Relation show unrelated rows

Anonymous — Tue, 31 May 2022 13:48:53 GMT

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

Re: One to Many Relation show unrelated rows

johnt75 — Tue, 31 May 2022 14:09:33 GMT

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

Re: One to Many Relation show unrelated rows

v-cazheng-msft — Fri, 03 Jun 2022 08:10:55 GMT

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

Re: One to Many Relation show unrelated rows

Anonymous — Tue, 07 Jun 2022 06:29:27 GMT

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

Re: One to Many Relation show unrelated rows

v-cazheng-msft — Tue, 07 Jun 2022 09:14:06 GMT

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