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.
Hello,
Here is an example of my situation - I have two tables with a one-to-one relationship.
Table X
Sally | 12 |
Tom | 30 |
John | 20 |
Doe | 12 |
Emily | 9 |
Bob | 2 |
And Table Y
John | @ |
Doe | # |
Emily | $ |
Bob | % |
I put Name from Table Y and Value from Table X together and I get
12 | |
30 | |
John | 20 |
Doe | 12 |
Emily | 9 |
Bob | 2 |
obviously I dont want the blank rows, just the names from Table Y and those values.
Solved! Go to Solution.
Hi @Connell ,
Create a measure as below:
_Value = IF(MAX('Table X'[Name]) in FILTERS('Table Y'[Name]),MAX('Table X'[Value]),BLANK())
And you will see:
For the related .pbix file,pls see attached.
Hi @Connell ,
Create a measure as below:
_Value = IF(MAX('Table X'[Name]) in FILTERS('Table Y'[Name]),MAX('Table X'[Value]),BLANK())
And you will see:
For the related .pbix file,pls see attached.
HI @Connell
Your issue is because of the join you have on both tables.
Double click on the tiny arrow as in picture above.
You have to select the first table second and second table as first in the "Edit Relationship window".
Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!
Regards,
Pranit
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Hi @Connell
you can do it with Power Query like this:
// Table X
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MyalU0lEyNFKK1YlWCsnPBXKMDcAcr/yMPCDPCMJzyU9FqHPNzQRrswTznPKTQOqUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Column1"}, #"Table Y", {"Column1"}, "Table Y", JoinKind.LeftOuter),
#"Expanded Table Y" = Table.ExpandTableColumn(#"Merged Queries", "Table Y", {"Column1"}, {"Column1.1"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Table Y",{"Column1.1", "Column2"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Column1.1] <> null))
in
#"Filtered Rows"
// Table Y
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXJQitWJVnLJTwWylcFs19zMnEogTwXMc8pPArJVlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}})
in
#"Changed Type"
Insert both M-Codes in advanced editor.
Regards FrankAT
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |