Filter data using multiple columns

[jcd]

This seems like it should be fairly simple but I am struggling to figure out how to do this with power bi. I essentially have the following structure.

Agent Table

AgentId, Name etc..

Transaction Table

transaction id, date, price, primary agent 1, primary agent 2, coop agent 1, coop agent 2

I want a slicer that will allow me to see all transactions an agent is involved in, meanig they could be a primary agent or a coop agent thus their agent id could be in any of those four columns. I want a text slicer where I can select the name from the agent table and it will fill a matrix with the transaction the agent was involved in. 

I cannot seem to get the filtering to work. I have gone down the path of having multiple relationships between the agent table and the transaction table, then used the following dax measure to "activate" the inactive relationships (Colist1 = CALCULATE(VALUES(biAgent[id]),USERELATIONSHIP(transaction[coop_id1],biAgent[id])) on the biAgent table.  Is this the correct path?  

Any insight is appreciated.

[Smauro]

Hey @jcd . I'm guessing the Transaction table is filled with AgentIds and not AgentNames. If not, the same measure would work for agent names.

The idea is that you create a slicer with the 'biAgent'[id] and then you calculate the sum of prices where the agent's id appears in one of the four columns (at least that's the calculation of my measure, you could do the same thing for whatever else you'd like to compute).

AgentSums =
VAR x =
    SELECTEDVALUE ( 'biAgent'[id], "Error" )
RETURN
    IF (
        x = "Error",
        x,
        CALCULATE (
            SUM ( [price] ),
            FILTER (
                'biTransaction',
                'biTransaction'[primary agent 1] = x
                    || 'biTransaction'[primary agent 2] = x
                    || 'biTransaction'[coop agent 1] = x
                    || 'biTransaction'[coop agent 2] = x
            )
        )
    )

It looks like this in the end:

[jcd]

Hi Smauro - Thanks for the hint. You don't happen to have a .pbix you can share? I am still relatively new with Power BI (couple of weeks) trying to understand the best approach to the tool.

[Smauro]

Sure @jcd ! Here you go: https://drive.google.com/open?id=14BQxJX6z4YD1wweLVaByMveQBNx-FXdI

[jcd]

Ah ok, I still had a relationship between the agent and transaction tables so I wasn't seeing the correct total. Much appreciated the tip, this seems like an good solution to my problem. I want to see if I can draw out totals such as "top 10 agents" for FY17 by dollar volume, with the corresponding transactions.

[v-huizhn-msft]

Hi @jcd,

After test, please mark the right reply as answer if you have resolved your issue. Please feel free to ask if you have other issue. Please give us respondence as soon as possible.

Thanks,
Angelia

[v-huizhn-msft]

Hi @jcd,

Slicer is used to filter rows in table. For your requirement, you need to create measures to get the transaction the agent was involved in. Please reference the detailed solution in the following threads, you can download the .pbix file to test.

Filter table based on two columns
How can I join tables with OR in condition on report 

Please feel free to ask if you have any other issue.

Best Regards,
Angelia

[jcd]

Thanks for the reply. I tried the solution and I can get it to sort as you described/demonstrated in your test.pbix. That being said, you cannot sum or report any meaningful totals using this approach. It seems to AND everything such that if you add a column such as say "deal value" and want to summarize the value of the deals across the 4 columns, it doesn't seem possible.