Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
Solved! Go to 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".
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
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 |
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)
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".
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |