Top N with additional conditions that have to be set via what if paramters

[Anonymous]

Hi all, 

I have the following situation and want to get your opinion on how to solve it best. I have a strong background in programming with Python but I am relatively new to PowerBI and Dax and this is my first post in this forum 🙂

- Say I have a table containing 100 clients and their revenues in sectors A, B, and C.
- I have what-if parameters in the Dashboard that allows the user to chose the relative importance of the sectors
- I already calculated a measure that uses the parameters dynamically and allows me to rank the customers according to that calculated value

Now comes the complication I want to solve: 
- I have another table containing information about all the distances between these customers.
(100x100 long and of the form ORIGIN,DESTINATION,DISTANCE: row1: A, B, Distance; row2: A,C,Distance,  ...)
- For Business reasons, I can only serve one customer within a radius of 50 miles.
- I want the algorithm to give me a list of the 10 best customers satisfying this additional condition.

Conceptually (and in Python) I would do it like that:
1) start with the best customer.
2) delete all customers within 50miles from the list of "potentially best customers"
3) from the list with the remaining candidates chose again the best
4) delete all of the customers within 50 miles from that customer
5....
6) do this until you have 10 customers. Done. 

How would you approach this in PowerBI using DAX and in order to have the flexibility to vary e.g. the max. allowed distance of 50 miles by the user dynamically.

Thanks a lot for your advice.

Best!

[Anonymous]

Hi Liu, 

Thanks for your reply. Is there a way to solve it without Python? For reasons out of my control, we cannot install it on that machine.

The data really is as simple as a list of IDs

ID	Rank (based on a score that bases on the selection of different parameters)
A	1
B	2
...	..
X	30

and a list of distances:

ID origin	ID destination	Distance in miles
A	B	40
A	C	80
A	X	100
B	A	40
B	D	120
...

Say, I set the rule to: no overlapping markets within 50 miles.

Then I want to exclude B from the top location list, because A has a higher score and B is in A's market and thus not a possible location anaymore.

I hope this helps. Any idea how to use for example virtual tables to solve this?

[Anonymous]

Hi  @Anonymous ,

Power bi desktop supports Python. You can try to use Python in power query to achieve your needs.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-in-query-editor

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.