topic Re: Count of rows not in related table in DAX Commands and Tips

Count of rows not in related table

Anonymous — Tue, 27 Aug 2019 15:22:04 GMT

Hi,

I have two tables with ID column common in both tables, i want to calculate count of ID's in current month from table1 based on createddate column which are not present in Table2

Table1 Table2

ID CreateDate ID

1 1/8/19 1

2 2/8/19

3 3/8/19

Output should be = 2

Thanks,

Pankaj

Re: Count of rows not in related table

v-anabat — Tue, 27 Aug 2019 17:58:45 GMT

Hi Anonymous ,

can you please provide few more examples for the dataset, also the expected result for it.

Regards,
Ananth

Re: Count of rows not in related table

Anonymous — Wed, 28 Aug 2019 08:12:10 GMT

Here is the example and expected output :

Table A Table B

ID CreatedDate ID

1 1/8/2019 14

2 12/8/2019

3 14/8/2019

4 1/7/2019

Wants to find out count of ID from table A which are not avialble in Table B for current month, assuming current month is august

Output should be : 2

Thanks,

Pankaj

v-anabat wrote:
Hi Anonymous ,

can you please provide few more examples for the dataset, also the expected result for it.

Regards,
Ananth

Re: Count of rows not in related table

v-anabat — Wed, 28 Aug 2019 10:20:57 GMT

Hi Pankaj,

If I understood correct, you are trying to find the count of Id's associated with current month in table1 and those Id's should not be present in Table2.

Check if the below solution works for you.

Create column in Table1 which is true if values in present in table2 and false if value is not present in table2.

Use the above column to create a measure for the Id's created this month in table1 and have calculated column set to false.

Column = VAR _v1 = RELATED('Table (3)'[Id])

RETURN IF('Table (2)'[ID] = _v1, TRUE(),FALSE())

Measure = VAR _v1 = FORMAT(TODAY(),"MMYYYY")

VAR _v2 = CALCULATE(COUNT('Table (2)'[ID]),

FILTER('Table (2)', FORMAT('Table (2)'[CreatedDate], "MMYYYY") = _v1 && 'Table (2)'[Column] = FALSE()))

RETURN _v2

Data:

Result:

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Regards,

Ananth