Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sarath5140
Helper I
Helper I

Count Rows from another table

Hi, Please help me in solving my below problem.

 

I have two tables:

 

Table A : 

 

CategorydescriptionId
FruitApple111
FruitBanana123
FruitGuava145
VegetableBrinjal121
VegetableCarrot167

 

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 
 
Column1Lookup
Category 
Fruit3
Others 
Vegetable2

 

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 :

 

Column1Lookup
Category 
Fruit3
Others 
Vegetable2

 

 Total                     5

 

 

 
1 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".

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi ,

 

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. 

 

Column1Lookupm_Total 2
Category 5
Fruit35
Others 5
Vegetable25

 

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

 

 

@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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 

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".

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.