Search code examples
filterpowerbidaxrelationshiplookup

Get the list of sending and receiving agents in two separated filters

 I have a fact table and a dimension in my model in Power BI:

ShipmentID SendingAgentCode ReceivingAgentCode
1000       12               13
1001       14               15

My model is like below: 

enter image description here

I want to have two filters one for Receiving Agents and one for Sending Agents. How can I achieve that?

Also, I want to calculate the number of shipments for Receiving/Sending Agents separately.

This is what I tried: 

SendingAgent = LOOKUPVALUE(Agent[AgentCode],Agent[AgentName],Summarize(Shipment,Shipment[SendingAgentCode]))
Solution

  • You cannot have 2 active relationships between tables. And even though you can have one active and the other inactive, it is better to delete the inactive one and create a separate table, e.g. DimSendingAgent = ALL(DimAgent).

    Now you can create the missing relationship to this new table. This is a common practice for relatively small tables using Power BI.