Distinct Count of 2 tables if they matches + specific value

[Tevon713]

Hi All... I have 2 tables.

Created a measure is to find distinct count of "Acct" in table 1:  

Count Unique Acct = DISTINCTCOUNT(Table1[Acct]) or should it be count row?

I also need help in trying to find the following:

1. Find matches of "Acct" in Table 2 to "Acct" in Table 1 if so then return distinct count or count row.

2. Second part similar to previous #1 but to find distinct count for specific region ie "East" in Table 2 is 2 as 2 matches to table 1 and "West" should be 1 and so on.

Thank you all for your help.

[v-yangliu-msft]

Hi  @Tevon713 ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTCOLUMNS('Table2',"1",[Acct])
var _table1=
SUMMARIZE('Table1','Table1'[Acct],'Table1'[Region],
"Flag",IF('Table1'[Acct] in _select,1,0))
return
IF(
    MAX('Table2'[Region]) in SELECTCOLUMNS(_table1,"Region",[Region]) ,
    COUNTX(FILTER(_table1,[Flag]=1),[Region]))

2. Result:

Best Regards,

Liu Yang

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

[Tevon713 · Accepted solution]

Thanks @v-yangliu-msft.

I was thinking something more along the line of the following, which appeared to work.

Matches Measure = CALCULATE(DISTINCTCOUNT(Table2[Acct]) , FILTER(Table2,LOOKUPVALUE(Table1[Acct],Table1[Acct], Table2[Acct])))

And for specific region be like this.... 

Matches Measure 2 = CALCULATE(DISTINCTCOUNT(Table2[Acct]) , FILTER(Table2,LOOKUPVALUE(Table1[Acct],Table1[Acct], Table2[Acct])), Table2[Region]="East")