Using a Slicer to Toggle Relationships for Multi-Address Scenario in Power BI

Question

In my Power BI model, I've set up a 'SalesRep' table containing [SalesPersonName] and their associated [Territories], as well as a 'Clients' table with [ClientCode], [Address1_territory], and [Address2_territory].

I've established two relationships:

Active relationship: 'SalesRep'[Territories] filters 'Clients'[Address1_territory]. Inactive relationship: 'SalesRep'[Territories] filters 'Clients'[Address2_territory]. My objective is to create a simple table visualization that displays [ClientCode] alongside the corresponding [SalesPersonName]. However, I face a challenge: I need a slicer that allows me to switch between 'Clients'[Address1_territory] and 'Clients'[Address2_territory].

I would greatly appreciate any guidance or advice on how to implement this slicer effectively in Power BI. Thank you.

I tried to create a new column "IF(SELECTEDMESURE .." but it creates static column not responding to slicer. May be I should duplicate one of my tables or use calculation groups? Chatgpt is hopeless

Answer 1

You need to use Measures for this work. SELECTEDVALUE is what you are looking for.

Create a similar measure to this one:

Client Count =
  IF(SELECTEDVALUE('your slicer table'[column]) = "Address1".
    COUNTROWS('Clients'),
    CALCULATE(COUNTROWS('Clients'), USERELATIONSHIP('SalesRep'[Territories], 'Clients'[Address2_territory]) )
  )

Then add this measure to your table visual.