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.
I have two tables that have a many to many relationship. I want to flag in table 1 if the key appears atleast 1 in table 2.
Example:
Table 1
ID Value
1 A
1 B
2 C
2 C
3 D
Table 2:
ID Value2
1 Ma
1 NA
3 SD
3 ED
4 SJ
5 WS
I want my output table which is essentially table 1 and a flag added to it if that ID ever appeared in table 2
ID Value Flag
1 A True
1 B True
2 C
2 C
3 D True
Is this possible to do in power BI?
Solved! Go to Solution.
Thank you both for your suggestions. Unfortunately, it did not work as it threw me an error saying:
"A single value for Table 1[ID] was not found".
Meanwhile, this is what I did -
I created a third table that had distinct values of Table 2 ID
I created a relationship between Table 1 IDs and the IDs listed in new table. This would now be a many to one join and will reflect if that ID exists or not.
Thank you,
HV
Hi @Anonymous ,
You can create the folllowing measure to add to a table visual:
Check ID= IF(LOOKUPVALUE(Table2[ID];Table2[ID];SELECTEDVALUE(Table1[ID]))=BLANK(); ""; "True")
Regards
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Thank you both for your suggestions. Unfortunately, it did not work as it threw me an error saying:
"A single value for Table 1[ID] was not found".
Meanwhile, this is what I did -
I created a third table that had distinct values of Table 2 ID
I created a relationship between Table 1 IDs and the IDs listed in new table. This would now be a many to one join and will reflect if that ID exists or not.
Thank you,
HV
Yep thats possible
Basically you can create a calculated column on the Table
Flag = If(COUNTROWS(FILTER(Table2,Table2[ID] = Table1[ID])) > 0 ,"True","")
You can also do this in Power Query using Merge Queries
let
....
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, Table2, {"ID"}, "Table2", JoinKind.LeftOuter),
#"Aggregated Table2" = Table.AggregateTableColumn(#"Merged Queries", "Table2", {{"ID", List.NonNullCount, "Count (Not Blank) of Table2.ID"}}),
#"Added Conditional Column" = Table.AddColumn(#"Aggregated Table2", "Flag", each if [#"Count (Not Blank) of Table2.ID"] > 0 then true else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Count (Not Blank) of Table2.ID"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Flag", "FlagUsingPowerQuery"}})
in
#"Renamed Columns"
Ive provided an example pbix file here
https://mega.nz/#!2BBUxCpS!p2sBSWQPOQRyCicUYsVOmgZlewfvWhhggP63R6LBNAo
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 |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |