Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
What is the correct syntax or a similar method to achieve a countif with a fixed reference at the first row
=COUNTIF(M$1:$M3,M3)
As you can see this formula expands as the row progresses... but how do I do it in M?
Solved! Go to Solution.
First add an index column. Next add a column that pulls the M column from the #"Added Index" step and use List.Select to pull all values matching M (of the current step). Use List.Count to count the number of rows. Now you can remove the INdex column. Query is below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyEYmEi8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [M = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
AddCountIf = Table.AddColumn(#"Added Index", "CountIf", each List.Count(List.Select(List.FirstN(#"Added Index"[M],[Index]), (el) => el = [M]))),
#"Removed Columns" = Table.RemoveColumns(AddCountIf,{"Index"})
in
#"Removed Columns"
First add an index column. Next add a column that pulls the M column from the #"Added Index" step and use List.Select to pull all values matching M (of the current step). Use List.Count to count the number of rows. Now you can remove the INdex column. Query is below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyEYmEi8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [M = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
AddCountIf = Table.AddColumn(#"Added Index", "CountIf", each List.Count(List.Select(List.FirstN(#"Added Index"[M],[Index]), (el) => el = [M]))),
#"Removed Columns" = Table.RemoveColumns(AddCountIf,{"Index"})
in
#"Removed Columns"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |