Allow Users to Override Measure Logic that Filters the Table

[Anonymous]

I have a table which matches companies based on similarity. It is constructed so that for each company, it will match against every other company, and give those matches a rank. eg.

Let's call this table 'Company Comparison'

Company	Matched Company	Rank
A	C	1
A	B	2
A	D	3
B	D	1
B	C	2
etc.

I then have a number of measures that, when the data is filtered on 'Company' eg. to Company A, will calculate the metric for the company matched with A at Rank 1 - so in the example above would calculate the metric for company C. The measures use the format:

CC Metric = CALCULATE([CompetitorMetric],'Company Comparison'[Rank] = 1)

What I want to do is create a slicer or similar where a business user can override this logic at will. So instead of always showing the metrics for Company C compared to A (because this is it's top match) the user could use a dropdown to say they thought A's closest competitor was B and they'd like to see the results for that instead. Ideally they could even select multiple and the competitor metrics would be for the data associated with all of those selected companies.

I'm stuck on how to acheive this. If I create a slicer on Matched Company in 'Company Comparison', the measures just won't work because the user might select a company that isn't Rank 1 and so the measure uses it's 'Company Comparison'[Rank] = 1 filter and finds no results.

If I try to change the measure so it looks for the highest rank remaining, instead of always rank '1' eg.

CC Metric = CALCULATE(VALUES('Company Comparison'[Competitor Metric]),FILTER('Company Comparison', 'Company Comparison'[Rank] = [Top Available Rank]))

this won't cover the multiple selection option.

Does anyone have ideas for how I might go about this?

[Greg_Deckler]

@Anonymous - Seems like an implementation of the Disconnected Table Trick. Basically, use a disconnected table as a slicer and then based on the selection in that slicer, perform calculations differently.

Here is one example - Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563

[Anonymous]

Thanks @Greg_Deckler , I'll have a read through 🙂

[Greg_Deckler · Accepted solution]

@Anonymous - The short form is:

Measure =
  SWITCH(SELECTEDVALUE('DisconnectedTable'[Column]),
    "Option 1", <calculation one way>,
    "Option 2", <calculation a different way>
  )

[amitchandak]

@Anonymous , not very clear. See if what if the parameter can help you

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

[Anonymous]

Thanks @amitchandak, the rank in this case is static (calculated outside powerbi and then brought in). So I guess what I'm trying to get at is if I have measures that filter on a column like that, but I'd also like a user to be able to override that and instead choose which rows they personally want to filter on, how do I code that into a measure? That it should let the user override it's filter if they want to?