Forum Discussion
Allow Users to Override Measure Logic that Filters the Table
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:
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.
Does anyone have ideas for how I might go about this?
Anonymous - The short form is:
Measure = SWITCH(SELECTEDVALUE('DisconnectedTable'[Column]), "Option 1", <calculation one way>, "Option 2", <calculation a different way> )
5 Replies
- amitchandak
Super User
Anonymous , not very clear. See if what if the parameter can help you
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?
- Greg_Deckler
Community Champion
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
Thanks Greg_Deckler , I'll have a read through 🙂
- Greg_Deckler
Anonymous - The short form is:
Measure = SWITCH(SELECTEDVALUE('DisconnectedTable'[Column]), "Option 1", <calculation one way>, "Option 2", <calculation a different way> )
