Count of rows not in related table

[Anonymous]

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

[v-anabat]

Hi @Anonymous ,

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

Regards,

Ananth

---

[Anonymous]

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

---

---

[v-anabat · Accepted solution]

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