How to avoid redundancy in a double table relationship scenario?

[johnnyirl]

Dear Power BI community,

There is a data model as follows:

When a user selects a property on any visual that uses the Property[name] attribute, there is a map visual that gets updated with only the properties (property_id_destination) that are within x kilometres of the origin property. So far so good, but I would to be able to control the size of the bubbles using the revenue, i.e. revenue of nearby properties. This is challenging, since there is already a relationship between Property and Nearby Properties, and this relationship is needed so that the map can be filtered on the origin property.

What is currently working: a new merged table with a separate copy of each property, and associated revenue and searches, called 'Nearby Properties Revenue'. Now, it's possible to create a relationship between this, and the Nearby Properties table. This however, goes against basic data archtiecture principles of introducing redundancy.

Have also tried filtering the Nearby Properties table dynamically, using the SELECTEDVALUE of Property[name], producing a filtered table which leaves it nicely to be associated to the Property table on the property_id_destination field. This doesn't work however, as from my knowledge it's not possible in DAX to dynamically replace the contents in a table at interaction-time, using a variable.

My question to the community is therefore, is there an alternative way of getting a handle on property[name], revenue, and searches of the nearby properties, without introducing redundancy?

Many thanks,

J

[amitchandak]

@johnnyirl , As property is joined to , Revenue, search and near by, it should filter data of all these (hope 1-M join)

Other wise the values from these tables to measures can help

example

calculate(Sum(revenue[Sales Amount]), filter(revenue, revenue[Property ID] in values(search[Property ID]) ) )

Treatas can also help

https://learn.microsoft.com/en-us/dax/treatas-function

How to filter the slicer of a disconnected table: https://youtu.be/cV5WfaQt6C8

[johnnyirl · Accepted solution]

Thanks @amitchandak ,

The link on how to filter the slicer of a disconnected table was very useful. This helped me achieve the filtering of the Nearby Properties table without using a relationship. To then get the revenue for the nearby properties, I added an inactive relationship between [Nearby Properties] and [Property], and then added the following measure on [Nearby Properties]:

sale_amount_nearby =

CALCULATE (

    SUM ( Revenue[sale_amount] ),

    CALCULATETABLE (

        Nearby Properties,

        USERELATIONSHIP ( Nearby Properties[property_id_destination], 'Property'[property_id] ),

        REMOVEFILTERS ( 'Property' )

    )

)

The following article was useful in formulating the above DAX: 

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/