Based Upon Selection, pull in one column of data that relates, but one column that doesn't

[mattwoldt]

Hello,

So I work in real estate and I am in a pretty tricky scenario. We have brokers that will do deals, but within those deals other brokers will help, what I am trying to accomplish is when you select a broker, it will show their deals and then also show the brokers that helped on said deal, but I need to do it in a dynamic way, because we have thousands of brokers. 

For instance, John Doe did a deal labeled as 1000, in that deal there were 4 other brokers. But when I select John Doe, it will only show John Doe on that deal 1000. 

The other tricky part is a broker doesn't just do 1 deal, they will do a multitude of deals. I just cannot for the life of me figure out how to do this part and it is really the only piece missing in my dashboard. 

Thank you!

[DataInsights · Accepted solution]

@mattwoldt,

This solution uses a disconnected table Brokers, which is a distinct list of brokers in the Deals table. This table should not have a relationship with the Deals table. Here's the calculated table:

Brokers = DISTINCT ( Deals[Broker] )

Next, create the following measure:

Visual Filter = 
VAR vRowCount =
    CALCULATE (
        COUNTROWS ( Deals ),
        ALL ( Deals ),
        VALUES ( Deals[Deal ID] ),
        TREATAS ( VALUES ( Brokers[Broker] ), Deals[Broker] )
    )
VAR vResult =
    IF ( vRowCount > 0, 1 )
RETURN
    vResult

The TREATAS function treats the slicer as if it were filtering the Deals[Broker] column (the filter is applied via DAX since there is no relationship). The slicer should use Brokers[Broker].

Create a table visual using fields from the Deals table, and add a visual filter using the measure above:

Result:

If you already have a Brokers table in your data model and are using a Broker slicer that controls various visuals, the technique in the article below can be used.

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/