Count in two related tables

[harakuruku]

So I have two related tables called units and opportunities. I have a total of 50 units and their status can be found in the opportunities. I created a conditional column for that status with three category Issued, Conditonal, Unconditional and the fourth one is Not Specified if they didn't indicate any status. The thing is, not all units can be found in opportunities table unless they were transfered to it. So my opportunities table has a total of 31 units only.  But when I display them in bar chart, PBI only counts those 31 units even though I used the units table as a value to count them. See photo:

Need to show the remaining units as Not Specified. THANK YOU! ♥

[v-yulgu-msft]

Hi @harakuruku,

I could get correct result in my test.

In your scenario, are there some repeat Unit records in units table? Please try to add measure CountUnit=Count('units'[UnitID]) into chart for a test.

If possible, please share your .pbix file so that I can check for you.

Regards,

Yuliana Gu

[harakuruku · Accepted solution]

@v-yulgu-msft @Ashish_Mathur Thanks for replying. Found the solution in my problem. In the end, I decided to duplicate both tables and merge the duplicated tables. It worked! I just have to create relationship between them so that everytime the original table is updated so is the duplicate. Thank you so much! 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.