Help with measure

[jbernard82]

I've got a table full of call records and I am creating a matrix to display total incoming calls and outgoing calls for each extension selected in the slicer. I wrote the following or the measure thinking that it would filter using the slicer only the values I need.

Calls In = COUNTROWS(FILTERS(Calls[ToNumber]))

For each individual selected in the slicer it shows the same number which I figured out is the total distinct values from the dataset instead of the total for each person selected in the slicer. I know Im missing something I just can't pinpoint the problem.

[konstantinos]

Just use

Calls In = COUNTROWS( Calls )

[jbernard82]

That returns all calls. I am trying to use a slicer to view calls for specific users.

[itchyeyeballs]

If you put the measure described by @konstantinos in the values section of the matrix and put the person details in the rows section you should get a count for each person.

If that doesn't work you may have a problem with your linking between tables, do you have a "relationships may be needed" warning above your field list?

[jbernard82]

When I do that I still get the same total call count for everything. I dont have any relationship errors but I had to change my model because the Calls table has a ToNumber and FromNumber and I cannot have two active relationships with the Extension column on the other table. Here is my current model.

[konstantinos · Accepted solution]

My suggestion that is what I would do ( although don't know your other visuals ) is to create an active relantionship Extension -  ToNumber  and inactive relantionship with Extension - FromNumber..The measures can be 

Calls in = COUNTROWS( Calls )

Calls Out =
CALCULATE (
    COUNTROWS ( Calls );
    USERELATIONSHIP ( Extensions[Extension]; Calls[FromNumber] )
)

This way you won't have to change your visual or your model.

[leonardmurphy]

The challenge with an inactive relationship is that @jbernard82 is using a slicer to filter the extension and then wants to see outbound and inbound calls for each extension. A slicer will always use the active relationship, so if an extension has only ever been a FromNumber, it would get filtered out. 

For that reason, I'd tend to agree with @itchyeyeballs regarding unpivoting the call data. 

If the filtering out wouldn't be an issue though, then an inactive relationship is definitely a more straightforward solution to the immediate problem of counting calls.

[jbernard82]

Thanks @konstantinos! That did the trick. The slicer does still appear to work with the inactive relationship.

[itchyeyeballs]

It looks like there may be an issue with your model, does the  "total calls by ext..." table combine the calls in and out?

If so using it to link through to the extentions table may cause issues.

Try changing the link from calls table to link directly to extensions.

I find this quite useful to describe how to set up tables and links - http://www.powerpivotpro.com/2015/10/giving-back-steal-this-reference-card/

[jbernard82]

The total calls by extensions does a union of a select all toNumber and FromNumber rows where they involve an internal extension so there could be two rows with the same CallID but two different extensions when two people call each other in the office.

Moving the relationship from the ToNumber on the Calls table directly to the Extensions table fixed it. The problem is I cannot add another relationship from the FromNumber to the Extentions table because it cannot have multiple relationships on the same column in the Extension table for the Calls Out measure.

[jbernard82]

I guess I could just duplicate the Calls table just to get the relationship for this to work but that could mess up other visuals unless creating a relationship between the two on the CallID field would take care of that. Thoughts?

[itchyeyeballs]

You need to get your model right before you go any further otherwise your measures will become ever more complex to get around underlying issues.

As mentioned in your other thread, I would look at unpivoting the calls table so that incoming and out going calls are separate rows. If every call has a unique Id you can avoid double counting internal calls by filtering in your measures