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.
Hi All,
I m new to pwer BI, please help on below.
count in a new column based on other 2 columns.
Appreciate your support here.
Server | RiskRank | Count |
Server1 | 10 | 2 |
Server1 | 10 | 2 |
Server1 | 20 | 3 |
Server1 | 20 | 3 |
Server1 | 20 | 3 |
Server1 | 50 | 1 |
Server1 | 60 | 4 |
Server1 | 60 | 4 |
Server1 | 60 | 4 |
Server1 | 60 | 4 |
Server2 | 5 | 1 |
Server2 | 40 | 3 |
Server2 | 30 | 2 |
Server2 | 60 | 2 |
Server2 | 30 | 2 |
Server2 | 50 | 1 |
Server2 | 40 | 3 |
Server2 | 60 | 2 |
Server2 | 40 | 3 |
Solved! Go to Solution.
@KiranGupta15 - If I understand correctly:
Count = COUNTROWS(FILTER('Table',[Server]=EARLIER([Server])&&[Risk]=EARLIER([Risk])))
If you want to get the count in another column by Power Query and keep your data model you may try my way.
I build a table like yours to have a test.
Duplicate the table, and group by then merge two tables.
One step you can get the counts.
Then merge two tables by Sever column.
Result:
M Query is as below, you can copy this in Advanced Editor.
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstMlTSUTI0UIrVwStgRLqAKbqAGYUCRiBD0fgm6AqM0QUwjMBQYYougGEohhkgFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, RiskRank = _t]),
#"Merged Queries" = Table.NestedJoin(Source, {"Server", "RiskRank"}, Table2, {"Server", "RiskRank"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Count"}, {"Table2.Count"})
in
#"Expanded Table2"
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If you want to get the count in another column by Power Query and keep your data model you may try my way.
I build a table like yours to have a test.
Duplicate the table, and group by then merge two tables.
One step you can get the counts.
Then merge two tables by Sever column.
Result:
M Query is as below, you can copy this in Advanced Editor.
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstMlTSUTI0UIrVwStgRLqAKbqAGYUCRiBD0fgm6AqM0QUwjMBQYYougGEohhkgFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, RiskRank = _t]),
#"Merged Queries" = Table.NestedJoin(Source, {"Server", "RiskRank"}, Table2, {"Server", "RiskRank"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Count"}, {"Table2.Count"})
in
#"Expanded Table2"
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
you can do it with Power Query like this:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstMlTSUTI0UIrVwStgRLqAKbqAGYUCRiBD0fgm6AqM0QUwjMBQYYougGEohhkgFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, RiskRank = _t]),
#"Inserted Merged Column" = Table.AddColumn(Source, "Merged", each Text.Combine({[Server], [RiskRank]}, ""), type text),
#"Grouped Rows" = Table.Group(#"Inserted Merged Column", {"Merged"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
@KiranGupta15 - If I understand correctly:
Count = COUNTROWS(FILTER('Table',[Server]=EARLIER([Server])&&[Risk]=EARLIER([Risk])))
User | Count |
---|---|
120 | |
69 | |
69 | |
57 | |
50 |
User | Count |
---|---|
167 | |
82 | |
68 | |
65 | |
53 |