Count occurrences of Yes values

[JoshEnglish]

I have merged two tables, a list of accounts against a  list of accounts in groups so I now have:

Account Group

------- -----

1       null

2       A

3       A

4       B

I am trying to create columns in the query that count the number of times each group appears in the merged table AND count the number of times the group appears in the original table of groups.

If the original Group table is:

Account Group

------- -----

2       A

3       A

4       B

5       B

I need columns:

Account Group Here There

------- ----- ---- -----

1       null  0    0

2       A     2    2

3       A     2    2

4       B     1    2

Eventually I need to have a single list of all Accounts where Group is null, and the Group value if Here = There, (i.e. 1, A, 4) but that's a different issue altogether.

Any suggestions?

[Ashish_Mathur]

Hi,

I am confused.  What are your inputs and what output do you want?  How did you get the numbers in the Here and There columns?

[JoshEnglish]

The numbers in the "Here" and "There"  columns are the columns I am trying to fill out with Power BI.

They are the equivalent of Excel table functions =COUNTIF([GID],[@GID]) and =COUNTIF(Table2[Group ST ID],[@GID]). 

[Zubair_Muhammad · Accepted solution]

@JoshEnglish

Try these Columns in MergedTable

There =
CALCULATE (
    COUNTROWS ( OriginalTable ),
    FILTER ( OriginalTable, OriginalTable[Group] = MergedTable[Group] )
)

Here =
CALCULATE (
    COUNTROWS ( MergedTable ),
    ALLEXCEPT ( MergedTable, MergedTable[Group] )
)