Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
Try these Columns in MergedTable
There = CALCULATE ( COUNTROWS ( OriginalTable ), FILTER ( OriginalTable, OriginalTable[Group] = MergedTable[Group] ) )
Here = CALCULATE ( COUNTROWS ( MergedTable ), ALLEXCEPT ( MergedTable, MergedTable[Group] ) )
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?
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]).
Try these Columns in MergedTable
There = CALCULATE ( COUNTROWS ( OriginalTable ), FILTER ( OriginalTable, OriginalTable[Group] = MergedTable[Group] ) )
Here = CALCULATE ( COUNTROWS ( MergedTable ), ALLEXCEPT ( MergedTable, MergedTable[Group] ) )