Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |