Filter a column based on a value form another column

[Anonymous]

Hi,

I've got 2 tables:

1. Addresses (Column A and C)

2. Person(Column B and D)

In the address table a person can have more addresses. The column is calld Type (red writen numbers)

In the person table there is a column named Person.PreferredAddresTypeId (yellow)

I've combined these two tables into one and now i want to filter the type column on the Person.PreferredAddresTypeId. as result i only have the preffed address of a person.

Any kind of help would be appreciated.

[jthomson · Accepted solution]

So you want to have it just showing those rows where column C is the same as column D? If so, just make a calculated column something like:

IsPreferredAddress = [Type] = [Person.PreferredAddresTypeId]

then filter that on true?

[Anonymous · Accepted solution]

@Anonymous 

I took some liberties with test data I used for this, but I think my solution might help. 

I have the following tables imported in my model:

Person:

Id	Type	Name
1	2	Chuck
2	1	Adam

Address:

Id	Type	Address
1	1	123 Schirra Court
1	2	456 Grissom Drive
2	1	213 Aldrin Circle
2	2	548 Pogue Way

After importing them into PBI, I used the following table expression to create a new table that pulled in the preferred address for each person:

SUMMARIZE Table = 
SUMMARIZE(
    Person,
    Person[Id],
    Person[Type],
    Person[Name],
    "Preferred Address",
    CALCULATE(
        SELECTCOLUMNS('Address',"Address",'Address'[Address]),
        FILTER(
            'Address',
            'Address'[Type] = Person[Type]
        )
    )
)

DAX with created table

Hope this helps!

Click HERE for the PBIX file.

CIick HERE for my address excel sheet.

Click HERE for my person excel sheet.

[jthomson · Accepted solution]

So you want to have it just showing those rows where column C is the same as column D? If so, just make a calculated column something like:

IsPreferredAddress = [Type] = [Person.PreferredAddresTypeId]

then filter that on true?

[Anonymous]

Thank you! I Will look at it after my vacation and leave a comment.

[v-diye-msft]

Hi @Anonymous ,

If above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,
Dina Ye