Desktop

[Anonymous]

Hello,

How to count records that are not appearing in other table joining by id?

Thanks, appreciate any help

[Anonymous · Accepted solution]

Hi, @Anonymous 

Thank you very much for your reply. In many-to-many relationships, related functions cannot be used. This is shown in the figure below:

Here's how I solved it:

record1 = 
VAR _recordintable2 = CALCULATE(MAX('Table 2'[record]),ALL('Table 2'[record]))
RETURN
CALCULATE(MAX('Table'[record]),FILTER('Table','Table'[record]<>_recordintable2))

Here are the results:

I've provided the PBIX file for this time below, and it would be great if it could help you. If you're not familiar with the DAX functions above, you can follow the links below to learn what they do and what they return.

ALL - DAX Guide

VAR keyword (DAX) - DAX | Microsoft Learn

VAR - DAX Guide

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Anonymous]

Hi Jianpeng, thanks for the quick response! When tried to use your measure got an error that column on table 2 or relationship between table 1 and table 2 doesn't exist. And i have relationship many to many for these tables with the same [ID].

We use Life connection in Power bi. I tried to use Related before and it always gives me an error. Any ideas?

Many thanks

[Anonymous · Accepted solution]

Hi, @Anonymous 

Thank you very much for your reply. In many-to-many relationships, related functions cannot be used. This is shown in the figure below:

Here's how I solved it:

record1 = 
VAR _recordintable2 = CALCULATE(MAX('Table 2'[record]),ALL('Table 2'[record]))
RETURN
CALCULATE(MAX('Table'[record]),FILTER('Table','Table'[record]<>_recordintable2))

Here are the results:

I've provided the PBIX file for this time below, and it would be great if it could help you. If you're not familiar with the DAX functions above, you can follow the links below to learn what they do and what they return.

ALL - DAX Guide

VAR keyword (DAX) - DAX | Microsoft Learn

VAR - DAX Guide

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Anonymous · Accepted solution]

Thank you so much!!

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Anonymous]

Hi, @Anonymous 

Based on your description, I have created the following sample data:

table1:

table2:

Relationship:

Based on your description, find out which records are not in Table 2:

DAX formula:

record1 =
CALCULATE (
    MAX ( 'Table'[record] ),
    FILTER ( 'Table', 'Table'[record] <> RELATED ( 'Table 2'[record] ) )
)

In Table Visual, we use the id field in Table1 and this measure, and the result is as follows:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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