Count based on 2 tables

[Anonymous]

Hi All,

Req: I have 2 tables ,I wanted to count fruits which has 1 or more fruits then count as 1 , if no fruits display as 0 and also  grand total also required

please don’t use any visual level filters likne fruit = blank because I have some other calculations based on this so looking for formula based on 2 tables

Please find attached data set:

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

You could use this formula to get it.

fruit count = var _table=SUMMARIZE(Table2,Table2[ID],"_value",IF( CALCULATE(COUNTA(Table2[Fruit]),FILTER(Table2,Table2[Fruit]<>BLANK()))>=1,1,0)) return
SUMX(_table,[_value])

CountID = CALCULATE(COUNTA(Table1[ID]))

Result:

here is pbix file, please try it.

Best Regards,

Lin