Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter a column based on a value form another column

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.

 

Need Help
  • jthomson's avatar
    jthomson
    7 years ago

    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's avatar
    Anonymous
    7 years ago

    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:

     

    IdTypeName
    12Chuck
    21Adam

     

    Address:

     

    IdTypeAddress
    11123 Schirra Court
    12456 Grissom Drive
    21213 Aldrin Circle
    22548 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.

4 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago
      Thank you! I Will look at it after my vacation and leave a comment.
      • v-diye-msft's avatar
        v-diye-msft
        Community Support
        7 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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:

     

    IdTypeName
    12Chuck
    21Adam

     

    Address:

     

    IdTypeAddress
    11123 Schirra Court
    12456 Grissom Drive
    21213 Aldrin Circle
    22548 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.