Issue With Filtering

[Connell]

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. 

[v-kelly-msft · Accepted 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.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[pranit828]

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

[FrankAT]

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