Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I am relatively new to Power BI. I'm trying to create a list that surveys a large table full of contact, geographic and policy info.
I have two tables. Table 1 starts with a text string of State-County-Policy # and then invidual locations with individual contact names and numbers. Often, there are repeats.
State/County/Policy | Location | Contact Name | Contact Number |
AK-ANCHORAGE-0001 | 1 | GUY SMITH | 9999999999 |
AK-ANCHORAGE-0001 | 2 | GUY SMITH | 9999999999 |
AK-ANCHORAGE-0001 | 3 | GUY SMITH | 9999999999 |
AK-ANCHORAGE-0001 | 4 | DAVID SMITH | 8888888888 |
AK-ANCHORAGE-0001 | 5 | DAVID SMITH | 8888888888 |
AK-ANCHORAGE-0001 | 6 | DAVID SMITH | 8888888888 |
AK-ANCHORAGE-0002 | 7 | PETER SMITH | 7777777777 |
AK-ANCHORAGE-0002 | 8 | PETER SMITH | 7777777777 |
AK-ANCHORAGE-0002 | 9 | PHIL SMITH | 6666666666 |
AK-ANCHORAGE-0003 | 10 | JOHN SMITH | 5555555555 |
Table 2 is a summarized version of this first table where I want to roll up table 1 into one State-County-Policy instance per line, with the contact name and contact number populated by the most common value that occured in each of those columns for that state-county-policy combination.
State/County/Policy | Contact Name | Contact Number |
AK-ANCHORAGE-0001 | ||
AK-ANCHORAGE-0002 | ||
AK-ANCHORAGE-0003 |
So, in other words I would want table 2 to look like this, selecting the most common values from filtering table 1 via the state/county/policy.
State/County/Policy | Contact Name | Contact Number |
AK-ANCHORAGE-0001 | GUY SMITH | 9999999999 |
AK-ANCHORAGE-0002 | PETER SMITH | 7777777777 |
AK-ANCHORAGE-0003 | JOHN SMITH | 5555555555 |
Is this possible?
Solved! Go to Solution.
Hi @michaeljhenley0 ,
You can create a calculated table as below:
Table 2 =
VAR _table =
SUMMARIZE (
'Table',
'Table'[State/County/Policy],
'Table'[Contact Name],
'Table'[Contact Number],
"countofcn", CALCULATE ( COUNT ( 'Table'[Contact Number] ) )
)
VAR _table2 =
ADDCOLUMNS (
_table,
"maxcount",
MAXX (
FILTER ( _table, [State/County/Policy] = EARLIER ( [State/County/Policy] ) ),
[countofcn]
)
)
VAR _table3 =
SUMMARIZE (
FILTER ( _table2, [countofcn] = [maxcount] ),
[State/County/Policy],
[Contact Name],
[Contact Number]
)
RETURN
_table3
Best Regards
Hi @michaeljhenley0 ,
You can create a calculated table as below:
Table 2 =
VAR _table =
SUMMARIZE (
'Table',
'Table'[State/County/Policy],
'Table'[Contact Name],
'Table'[Contact Number],
"countofcn", CALCULATE ( COUNT ( 'Table'[Contact Number] ) )
)
VAR _table2 =
ADDCOLUMNS (
_table,
"maxcount",
MAXX (
FILTER ( _table, [State/County/Policy] = EARLIER ( [State/County/Policy] ) ),
[countofcn]
)
)
VAR _table3 =
SUMMARIZE (
FILTER ( _table2, [countofcn] = [maxcount] ),
[State/County/Policy],
[Contact Name],
[Contact Number]
)
RETURN
_table3
Best Regards