Count Rows from another table

[sarath5140]

Hi, Please help me in solving my below problem.

I have two tables:

Table A : 

Category	description	Id
Fruit	Apple	111
Fruit	Banana	123
Fruit	Guava	145
Vegetable	Brinjal	121
Vegetable	Carrot	167

Table B : 

Category

Fruit

Fruit

Fruit

Vegetable

Vegetable

Others

Now i wanted output like to get the count of id's present in table A when Category value matches with Table B with Table A category value. 

I am able to acheive the same with below calculated column DAX 

Lookup = CALCULATE(COUNTROWS(TableA),FILTER(TableA,TableA[Category] = TableB[Category])) and my output is as expected like this 

Column1	Lookup
Category
Fruit	3
Others
Vegetable	2

But i need my total to get it displayed for the output table. I have already enabled Totals for my table.

Expected Output should be :

Column1	Lookup
Category
Fruit	3
Others
Vegetable	2

 Total                     5

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[sarath5140]

Hi Greg_Deckler,

Thanks for your reply.

I have tried the below measure in my pbix file but unable to get the total for my category count. Could you please check the below measure and let me know where I am going wrong. 

I am getting the total but the same total is repeating for all the rows. 

Column1	Lookup	m_Total 2
Category		5
Fruit	3	5
Others		5
Vegetable	2	5

m_Total 2 =

VAR __table = SUMMARIZE(Category,Category[Category],"__value",DISTINCTCOUNT(Category[Id]))

RETURN

IF(HASONEVALUE(Category[Category]),DISTINCTCOUNT(Category[Id]),SUMX(__table,[__value]))

Thanks

[Greg_Deckler]

@sarath5140 - First, apologies, I missed that this was a calculated column. You actually shouldn't need to even use a Lookup column actually if you relate the tables. I presented a couple different options though in the attached file. TableA and TableB on Page 2. Here is the measure form:

Measure 2 = 
VAR __sum = SUMX(ALL('TableB'),[Lookup])
RETURN
IF(HASONEVALUE('TableB'[Category]),MAX([Lookup]),__sum)

[sarath5140]

Thanks for the example. I understood this works if both the tables are related. In my original case, both the tables are not related to each other. If tables are not related then how could i acheive this with pure DAX.

Thanks,

Sarath 

[Greg_Deckler · Accepted solution]

It's your Lookup column formula, the measure is the same. See my updates, I created a "TableA Unrelated", your Lookup column as "Lookup Unrelated" and my measure which is identical other than using "Lookup Unrelated".