Forum Discussion
put value in column if record exist in other table with conditions in that table
Hi Guys,
figgering out powerbi and setting up a ranking system in an existing powerbi with tables we already got. the thing is i get gaps in my ranking because players did not participate on a matchtype so it jumps from 2 to 4 and skips 3 or it starts at 2 instead of 1
we gave people based on level an ranking and filtering in the powerbi.
Solution get another column in ranking or actualy two where we filter on matchtypeid
I already added gender and leeftijd from players and check if the age is under or equal to 18 and put an X in.
I would like to do the same with matchtypeid for lets say singleplay and doubleplay to put an x in the column when found.
So the names are not ideal sorry.
we have the tabel #"bond_jeugd_competitie_samen ranking"
with memberid in it
| memberid | enkelranking | dubbelranking | gender | leeftijd | jeugd |
| 1234 | 0.2 | 0.5 | M | 20 | null |
| 1235 | 0.4 | 1 | M | 18 | X |
| 1236 | 0.5 | 2 | V | 17 | X |
| 1237 | 1 | 2.6 | V | 33 | null |
and tabel #"bond_jeugd_competitie_samen rankinglog" i won't bother you with all the **bleep** over there. just a few columns the rest isn't important.
| memberid | matchtypeid |
| 1234 | HE |
| 1234 | HD |
| 1236 | DE |
| 1237 | DD |
| 1237 | DD |
| 1234 | HD |
| 1236 | E |
So the final table i'm looking for is
| memberid | enkelranking | dubbelranking | gender | leeftijd | jeugd | singleplay | doubleplay |
| 1234 | 0.2 | 0.5 | M | 20 | null | X | X |
| 1235 | 0.4 | 1 | M | 18 | X | null | null |
| 1236 | 0.5 | 2 | V | 17 | X | X | null |
| 1237 | 1 | 2.6 | V | 33 | null | null | X |
so in the end we can do something like this
rankingdubbelspeljeugdopgeslacht = VAR CurVal = [dubbelranking]
RETURN
CALCULATE(
RANKX( 'bond_jeugd_competitie_samen ranking', [dubbelranking], CurVal, ASC, Dense ),
ALLEXCEPT( 'bond_jeugd_competitie_samen ranking','bond_jeugd_competitie_samen ranking'[gender],'bond_jeugd_competitie_samen ranking'[jeugd] )
)
and just add singleplay or doubleplay in the ALLEXCEPT
But the satement to get the X in singleplay and doubleplay is breaking my head.
we have 3 values for singleplay "E" "HE" and "DE" and 4 for doubleplay "HD" "DD" "GD" and just "D"
There is an relation on memberid ranking 1 rankinglog many
Hope somebody can help
Hi ravanzaanen,
Power Query Solution:
Result
let Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTLQMwKTpkDSF4iNDIBEXmlOjlKsDliRKVgapNQQqsTQAkhEwOTN4NpBBoWB5M2R5c2hOo30zKDyxsZwK2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [memberid = _t, enkelranking = _t, dubbelranking = _t, gender = _t, leeftijd = _t, jeugd = _t]), Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUfJwVYrVQfBcYDwzIM8FLmcO4rlg52HqA2qLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [memberid = _t, matchtypeid = _t]), MergedQueries = Table.NestedJoin(Table1, {"memberid"}, Table2, {"memberid"}, "Table2", JoinKind.LeftOuter), Ad_SinglePlay = Table.AddColumn(MergedQueries, "singleplay", each if List.ContainsAny([Table2][matchtypeid], {"E", "HE", "DE"}) then "X" else null, type text), Ad_DoublePlay = Table.AddColumn(Ad_SinglePlay, "doubleplay", each if List.ContainsAny([Table2][matchtypeid], {"HD", "DD", "GD", "D"}) then "X" else null, type text), RemovedColumns = Table.RemoveColumns(Ad_DoublePlay,{"Table2"}) in RemovedColumns
5 Replies
- dufoq3
Community Champion
Hi ravanzaanen,
Power Query Solution:
Result
let Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTLQMwKTpkDSF4iNDIBEXmlOjlKsDliRKVgapNQQqsTQAkhEwOTN4NpBBoWB5M2R5c2hOo30zKDyxsZwK2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [memberid = _t, enkelranking = _t, dubbelranking = _t, gender = _t, leeftijd = _t, jeugd = _t]), Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUfJwVYrVQfBcYDwzIM8FLmcO4rlg52HqA2qLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [memberid = _t, matchtypeid = _t]), MergedQueries = Table.NestedJoin(Table1, {"memberid"}, Table2, {"memberid"}, "Table2", JoinKind.LeftOuter), Ad_SinglePlay = Table.AddColumn(MergedQueries, "singleplay", each if List.ContainsAny([Table2][matchtypeid], {"E", "HE", "DE"}) then "X" else null, type text), Ad_DoublePlay = Table.AddColumn(Ad_SinglePlay, "doubleplay", each if List.ContainsAny([Table2][matchtypeid], {"HD", "DD", "GD", "D"}) then "X" else null, type text), RemovedColumns = Table.RemoveColumns(Ad_DoublePlay,{"Table2"}) in RemovedColumnsThanks for the help dufoq3
I only not see where you put the in list and if i change to my data somehow i get less X then expected
It should be an or list so if it contains E or HE or DE then X else null for singleplay But beside i put in the wrong forum i love to learn from this solution also. 😉- dufoq3
Hi, as you can see it works with sample data. Provide new sample (data from your screenshot) and I can check.
