set analysis modifier with counts

[Anonymous]

Hi,

I have data which is sites, ports, and services

• A site has multiple ports
• A port belongs to one site
• Ports to Services is many to many

I have a table with sites as the dimension.

I want to add a count of the # of ports on that sites that carry all services that cross the site.

So for example,

if Site Alpha has 4 ports and 6 Services with the following distribution:

• Port A carries 2 services
• Port B carries 1 service
• Port C carries 6 services
• Port D carries1 service

My table should report:

Site A   1

(counting only Port C)

I am using the following to count ports with a hard-coded number of services:

     count (distinct {$<[port]= {"=count( distinct [service]) = 6"}>} [port])

Now I am stuck trying to replace the hard-coded "6" with a count of the total number of services on the site for the specific row.

Thanks for any help you can provide!

[sunny_talwar]

So how do you know that Port C has all the the services? May be, for example, this is what we have

Port A - a, b

Port B - c

Port C - b, c, d, e, f, g, h (Doesn't have a)?

Or is that not possible?

[Anonymous]

In the set up at the beginning, I specified that Site A has 4 ports and 6 services. In your example, the site would have to have 8 services (I think you meant to put 7).

I could add a measure to my table of count(distinct [service]) and it will return 6 in my example.

[sunny_talwar]

So in total there can ever be 6 services? If that is true, what is the issue in hard-coding 6? I mean unless it can change, what is the issue in hard-coding?

[Anonymous]

Sorry, I guess I wasn't very clear. That was just meant to be one example with one site. I used a hard coded value because I was trying to figure out the expression one piece at a time. The actual data has thousands of sites with a variable number of services.

Let me expand my example:

Site A

    Port 1 has services: a,b

    Port 2 has services: c

    Port 3 has services: a, b, c, d, e, f

    Port 4 has services: e

Site B

    Port 1 has services: a, b, c

    Port 2 has services: b, d

    Port 3 has services: a, c, d

My Table should look like this (I've added an extra column)

Site ID	Total # Services	# Ports with all services
Site A	6	1
Site B	4	0

I can get the total number of services column using count(distinct [service]).

I just have not been able to use this within the modifier for the # of Ports column to get the total number of services per site:

     count (distinct {$<[port]= {"=count( distinct [service]) = 6"}>} [port])

Thank you.

[sunny_talwar · Accepted solution]

Give this a shot:

=Count(DISTINCT {$<[port]= {"=Count(DISTINCT [service]) = Aggr(NODISTINCT Count(DISTINCT service), site)"}>} [port])

I making an assumption here, you can tell me that this is not true and we can look for an alternate solution.

Assumption: No two site will have a common port. For instance

Site1

     Ports - a, b, c, d

Site2

     Ports - e, f, g, h (this site won't have any port names which are in Site1)

[Anonymous]

Thanks again, Sunny!