Using Slicers with Measures to select 'best' option and create summations of related data

[BicycleBox]

Hi all,

I'm relatively new to Power BI and have been making pretty steady strides, but have hit a bit of a wall with some DAX I have been trying to piece together, Measures are still quite opaque to me.  I'd really appreciate it if someone could help me out and perhaps explain the resulting DAX (assuming what I am after is achieveable! 🙂 ).

To outline the scenario (using my simplified data):

I have Servers in a variety of Cities.
I have Countries which have Pings to each of these Cities.
I have Users in a Country.

I would like to be able to create a visualisation where I can en/disable Servers, and then discover two pieces of information:

# How many Users are served by the active Servers (assuming a User uses the active Server with the best (i.e. lowest) Ping for them, being careful not to count someone twice if they can reach more than one Server with the same ping).
# In a table of Users, show which Server City and the Ping they would be getting based on their best available active Server.
 - As a desirable but not necessary extra I would be interested in seeing what a query might look like if we were to also try and see what their 'fallback' Server would be if their 'best' Server was not available.

As an additional Slicer I've been looking at introducing a 'MinimumViablePing' which will also remove a Server-Country entry if their Ping is not good enough.  I have some parts of this working but the above queries have hit my limit, I'll include CSVs for source data and my sample tables with DAX Measures so far below.

Source data:

Servers table:

ServerCity

Cardiff

Edinburgh

London

UserCountries table:

Country

England

Scotland

Wales

Users table:

Username	Location
Alpha	England
Bravo	Scotland
Charlie	Scotland
Delta	Wales
Echo	Wales
Foxtrot	Wales

Pings table:

SourceCountry	TargetServer	Ping
England	Cardiff	2
England	Edinburgh	2
England	London	1
Scotland	Edinburgh	1
Scotland	London	2
Wales	Cardiff	1
Wales	Edinburgh	2
Wales	London	3

Here are some visualisations I have managed to make with my data so far.

All servers active (note, no Ping from Scotland to Cardiff so Scotland has one fewer ViableServer)


Only Cardiff and Edinburgh (note England's Min Ping is now 2)

Reduced Max Ping (note Wales can no longer reach London, reflected in ViableServerCount)


DAX for current Measures:

ViableServerCount = COUNTROWS(FILTER(Pings, Pings[Ping] >= 0))
MinPing = MIN(Pings[Ping])

Here are some sample result tables I would like to make using the Slicers I have above (Active Servers selection, and Ping Range), the numbered title indicates options selected on the Slicers:

 Note on example 3, Alpha could equally have been directed to Cardiff, so long as they are in one of the groups this is OK, I am assuming we would naturally select on first/last entry based on sheet layout)

Apologies for the length of this, I wanted to provide as much information as I could in the hopes of being as clear as possible!

Any help would be very much appreciated it, I'd love to work through any resulting solution to try and understand how we get there. A lot of what I've been looking up and reading about for Measures is centred around financial and date based analysis with lots of summing whereas my aiming to get a Minimum Viable option has proven slightly perplexing.

Thanks in advance!

[v-piga-msft]

Hi @BicycleBox ,

Thanks for your description, but I still a little confused about your requirement.

From I unserstood, it seems that you want to create a slicer for en/disable Servers so that you could get the filtered data with the slicer?

What is your desired output for your data sample? Is the last image in your post your desired output? If not, please share your expected output so that I could understand your logic better.

If it is convenient, could you share your data sample with table format so that we could copy and test on it?

Best  Regards,

Cherry

[BicycleBox]

Hi @v-piga-msft ,

Thank you for your response!  Sorry, I couldn't see how to add tables in the initial text editor, the option to edit the table came up once I'd added it in the HTML view, I've updated my post with those now, and hopefully clarified some parts. 🙂 

Yes, the idea is to determine which users will have a valid connection and to which server depending on which servers we have active, and whether that user can reach them with a good enough ping.  This is reflected in my sample results by the two tables:
The left shows which server each user would connect to (the one that is active, that they have the lowest ping for).
The right shows us a count for each server of how many users are assigned to that server (those who can reach it, and it is their lowest ping).

I hope that helps clear things up, do ask if I'm still confusing matters and I can try to provide more specifics!

Thanks!

[BicycleBox]

I have made some headway with this, though could still use some help to get my final queries over the line. 🙂 

Referencing this post:
https://powerpivotpro.com/2015/07/rankx-with-ascending-order-to-show-lowest-quotes-by-vendors/

Translating the equivalent values of Server to Vendor, Quote to Ping and Product to Country.

I have managed to construct a visualisation such that for a given country I can show which server is best for it:

I'd like to focus on the right-hand table I am trying to achieve for now.

I cannot work out how to integrate the results of these values in some new measures to get the final tables and counts I am looking for.  I feel like I need some DAX in a Measure to merge together a new table which will give me a Username, their Country, and its RankedServerName, and then, placing ServerName and my new measure in a table it should count the rows of the matching RankedServerName, which should give me the right-hand table I was trying to create.

To try and provide another way of describing this for clarity: in the context of the original tutorial I linked, I would guess this would be equivalent to finding, for each Vendor, every Product for which they provided the best quote, and then taking it a step further beyond their model, perhaps consider an additional table of Customers each of which would like to buy a single Product, and then we could work out how many customers would ultimately be served by buying from each Vendor.