Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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] ) )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
78 | |
62 | |
61 | |
60 |
User | Count |
---|---|
166 | |
114 | |
99 | |
73 | |
65 |