Use column references in the filter of CALCULATE

[Anonymous]

Hello

I want to do a calculation:

CALCULATE(COUNTROWS(Table1),Table1[Column2]=Table2[Column1])

However it says the expression can only contain one column. Is there any workaround for this?

For example Table1:

1, A

2, B

3, C

Table2:

A

B

C

I want to count the rows of Table1 using as criteria Table1[Column2]=Table2[Column1] instead of hardcoding the criteria as Table1[Column2]="A" etc

Thanks

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,
Icey

[Anonymous]

Hi, unfortunately it is not solved. I get multiple errors of different types.

[amitchandak]

@Anonymous , In case they join on the same column you do not need that

else try

CALCULATE(COUNTROWS(Table1),filter(Table1,Table1[Column2]=max(Table2[Column1])))

CALCULATE(COUNTROWS(Table1),filter(Table1,Table1[Column2]=related(Table2[Column1])))

Row context need to forced using values or summarize when you check data above the match level

CALCULATE(COUNTROWS(Table1),filter(Table1,Table1[Column2]=max(Table2[Column1])),values(Table1[Column2]))

[ryan_mayu]

@Anonymous 

I changed your sample data because all the three letters can be found in both tables.

Then I will find how many letters in table 1 can be found in table 2

Measure 2 = 
VAR TBL=ADDCOLUMNS(SUMMARIZE(Table1,Table1[index],Table1[letter]),"MATCH",LOOKUPVALUE('Table2'[LETTER],Table2[LETTER],'Table1'[letter]))
VAR tbl2=FILTER(TBL,[MATCH]<>"")
return COUNTROWS(tbl2)

hope this is helpful.

[Tahreem24]

@Anonymous ,

Try the below DAX:

Column = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table2),Table2[Name] = RELATED(Table1[Name])))

It will give you 3 in output.