Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowGet inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.
I want to compare the text strings in four columns. If the string is the same in all four columns, then the score is 6. If none of the strings match, then 0.
I can create six conditional columns for each of the pair combinations, and then add up the score.
I was hoping to do this in one step.
Something like this.
= Table.AddColumn(source, Score, each
vAB = if [A] = [B] then 1 else 0,
vAC = if [A] = [C] then 1 else 0,
vAD = if [A] = [D] then 1 else 0,
vBC = if [B] = [C] then 1 else 0,
vBD = if [B] = [D] then 1 else 0,
vCD = if [C] = [D] then 1 else 0,
vScore = vAB + vAC + vAD + vBC + vBD + vCD
)
I prefer to do this in Powerquery instead of DAX.
I am probably missing a simple syntax.
Thank you for any suggestions.
Solved! Go to Solution.
Whichever way you approach this, I think there will be a a few steps to the calculation.
However, to write it in a general way, I would do something like this:
= Table.AddColumn(
Source,
"Score",
each let
ValuesList = {[A],[B],[C],[D]}, // Could rewrite if you need it to be dynamic
ValuesColumn = Table.FromList(ValuesList, each {_}),
GroupedCount =
Table.Group(ValuesColumn,{"Column1"},{"Count", Table.RowCount, Int64.Type})[Count],
PairsCount =
List.Sum(List.Transform(GroupedCount, each Number.Combinations(_,2)))
in
PairsCount,
Int64.Type
)
There could be a smarter or less verbose approach - will give it some more thought 🙂
Regards
Hi @MarkusEng1998, different approach here.
You have to define column names to calc here:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcKxOggRIzCGiRjAMUjECMpDqDHAqsYYiE2B2ARujjFUF1AkFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
ColumnNamesToCalc = List.Buffer({"A", "B", "C", "D"}),
ColumnCombinations = List.Buffer(List.Accumulate(
List.Zip({ {0..List.Count(ColumnNamesToCalc)-1}, ColumnNamesToCalc }),
{},
(s,c)=> s & [ x = List.RemoveMatchingItems(List.Skip(ColumnNamesToCalc, c{0}+1), {c{1}}),
y = List.Repeat({c{1}}, List.Count(x)),
z = List.Zip({ y, x })
][z]
)),
ChangedType = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
Ad_Result = Table.AddColumn(ChangedType, "Result", each
List.Sum(List.Transform(ColumnCombinations, (x)=>
[ a = Record.FieldOrDefault(_, x{0}, null),
b = Record.FieldOrDefault(_, x{1}, null),
c = if a = b then 1 else 0
][c] )), type number)
in
Ad_Result
Whichever way you approach this, I think there will be a a few steps to the calculation.
However, to write it in a general way, I would do something like this:
= Table.AddColumn(
Source,
"Score",
each let
ValuesList = {[A],[B],[C],[D]}, // Could rewrite if you need it to be dynamic
ValuesColumn = Table.FromList(ValuesList, each {_}),
GroupedCount =
Table.Group(ValuesColumn,{"Column1"},{"Count", Table.RowCount, Int64.Type})[Count],
PairsCount =
List.Sum(List.Transform(GroupedCount, each Number.Combinations(_,2)))
in
PairsCount,
Int64.Type
)
There could be a smarter or less verbose approach - will give it some more thought 🙂
Regards
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
27 | |
25 | |
25 | |
13 | |
9 |
User | Count |
---|---|
24 | |
19 | |
16 | |
13 | |
9 |