Filter a table on KeyColumn - Return all rows where another Column is the same value as the selected

[Anonymous]

Hi Community,

I have a problem regarding returning rows where Value in one column is the same as Value on selectedvalue. Note that I only have 1 table to filter on in order to get the ZipCode and the same table to return rows from.

Consider the following Dimension table:

1

The user of the report may search on the ID column in order to only select 1 row. Lets say user select ID 4 with ZipCode 1234. The report should then show all the address's on a map visualization with the same ZipCode as the selected person, in order to show persons in close proximity with the selected person.

The resulting table would look like this:
All records with the same ZipCode as the selected person (ID 4).
2


I have tried with the following measure:

VAR ZipC = SELECTEDVALUE('TablePerson'[ZipCode]')

RETURN
CALCULATE(
    COUNTROWS('TablePerson');
    FILTER(ALL('TablePerson'); 'TablePerson'[ZipCode] = ZipC)
)

The resulting measure should then (in my head) return a rowcount on each row that has the same ZipCode as the selected value, in this case ID 4 with ZipCode 1234. This measure I would then be able to place as a visual filter on the table and map and only show those with rows > 0.

I have a slicer with ID and has turned filtering of on table 2 in order to not only get the selected row.

So in a nutshell:

Slicer with ID. User select ID. Map visualization show all locations with the same ZipCode as the user selected ID has.

I have been at this problem for quite some time now and can't seem to get any closer to achieving this.

If anyone out there has had this problem before or problems like it, please help.

Thank you dearly,

KonkretJBU.

[sturlaws · Accepted solution]

Hi @Anonymous ,

in Power BI it is not possible to separate filter context coming from rows/column/visual vs slicers/other visuals.

To solve your issue, you need to create a new table containing the IDs, without any relationship to the first table. Then you can create a measure like this:

Measure =
VAR currentZIP =
    CALCULATE (
        MIN ( 'Table'[ZIP] );
        FILTER ( ALL ( 'Table' ); 'Table'[id] = SELECTEDVALUE ( 'ID_table'[id] ) )
    )
RETURN
    COUNTROWS ( CALCULATETABLE ( 'Table'; 'Table'[ZIP] = currentZIP ) )

where ID_table is the table containing only the IDs

In the filter-section of you visual set Measure greater than or equal to 1:

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.