Count of rows based on the id column between two tables

[Anonymous]

I have 2 tables

table1

ID 
1
2
3
4
5
6
7

Table 2

ID       code
1       56265001
1       38341003
1      56265001
2      709044004
2     13645005
3     73211009

Table1 - id column is primary key
table2- id column is foreign key
Requirment:-

if table2 count of code >= 2  based on the id column then "categroy1"
if table2 count of code = 1 based on the id column then "category2"
if no any entry present in the table 2  based on the id then "category3"

output:-

Type             count of id

Category1          2
category2            1
category3            4

[amitchandak · Accepted solution]

@Anonymous , Try a new column in table 1

new column =
var _cnt =countx(filter(Table2,table1[ID] = table2[ID] ),Table2[Code])+0

return
switch(true(),
_cnt=0, "category3"
,_cnt=1, "category1"
,"category2")

[Anonymous]

@amitchandak  Thank you .Its working..

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[dedelman_clng]

Hi @Anonymous , try this

Category = 
var __cnt = CALCULATE(COUNTA(Tab2[Code]), FILTER(Tab2, Tab2[ID] = Tab1[ID]))
return
SWITCH(TRUE(),
__cnt >=2, "Category 1", __cnt=1, "Category 2", "Category 3")

Hope this helps

David