Relationship and Cross Filtering not working quite right (Trying to condense to one row)

[BroomJ]

I have a table where Skype call data is stored (in the standard Skype LcsCDR database. When a response group gets called, the database creates a row for each individual user in the group, and specifies on that user’s row if they were the one to answer it. What I want to do is to strip out all the rows other than the person who answered. If no one answered, I only want to have one row (doesn’t matter which), just to show that a call came through and wasn’t answered for counting/averaging purposes (percentage of unanswered calls etc).

The other option is that there is another table with only one row in it that I have linked to the table mentioned above. I have created a relationship in PowerBI using the [StartTime]-[SequenceID] in both tables to link them together and set filtering to work in both directions. However, when I filter it doesn’t seem to work? Getting this working would also be a solution, but I’m not sure why it doesn't filter. If I filter on the table with one row, the table with multiple rows shows no results (even though there are matches), whereas if I choose one of the multiple rows, the table with one row in doesn’t filter at all (it just displays all records). I can’t see any discrepancies between the two columns the relationship is built on, they look identical to me. Here's some screenshots:

Selecting left table returns no results on right table:

Selecting right table doesn't filter left table:

You should be able to see in that images that the SDId and OCId seemingly match, so I'm not sure why it isn't working.

I did have a quick search on the forums to see if anyone else had experienced similar but didn't find anything matching my experience, but if there is another answer elsewhere that you're aware of, feel free to send it my way.

Hopefully this makes sense, but if not, please let me know and I'll try and provide further clarification.

Any help would be greatly appreciated.

[v-jiascu-msft]

Hi @BroomJ,

I have created a test mode, which works fine. It seems everything is good in your scenario. Please do some trouble-shooting.

1. Upgrade Desktop to the latest version though this couldn't be the cause. (Version: 2.49.4831.521 64-bit (August 2017))

2. How did you create the compound key? [StartTime]-[SequenceID]

3. Are there any other filters? Such as "Visual level filter".

4. Check the relationship if it connected the right columns. 

5. If it's possible for you to provide the file (PBIX) with dummy data?

Best Regards!

Dale

[BroomJ]

Hi Dale,

My Power BI desktop is currently running the latest version.

To create the compound key, I do the following:

(convert(varchar(32), SD.SessionIdTime, 126) + '-' + cast(SD.SessionIdSeq as varchar(10))) as SDId

(convert(varchar(32), OriginCall.SessionIdTime, 126) + '-' + cast(OriginCall.SessionIdSeq as varchar(10))) as OCId

There are no other filters, as shown below:

The relationship looks right to me, as far as I can tell:

Unfortunately, I don't know how feasible it will be to provide a PBIX with dummy data, but if you are unable to assist further I will see what I can do.

Thank you for your help!

[v-jiascu-msft · Accepted solution]

Hi @BroomJ,

Your model is quite clear. That makes this issue more weird. 

1. Why are the relationship inactive?

2. Add two slicers of columns "SDId" and "OCId". See how these two tables will act.

Best Regards!

Dale

[BroomJ]

Hello Dale,

That was the problem!

I had another table linked that was set as an active relationship, which was preventing me from setting the two I needed as active. I removed the active relationship on the other table and set the SDId and OCId tables to active and it now works.

Thank you very much for you help.