How can I make filtering work between these tables?

Question

Basically I want to filter to see the sales per call duration(Calls table). With the existing data model, when I try to visualize the data it gives me the wrong values as seen below.

I have connected the Sales table to the users table through the Username column and the Users table is connected to the Calls table through the username as well. I cannot directly connect the Sales to the Calls table using the Username column because it would be a many to many relationship.

I cannot figure out how to get the visual to give me the correct values. Previously I thought of just merging the sales table to the calls table but that would cause the sales data to be duplicated and give me inaccurate results so what can I do in a situation like this, perhaps there is a DAX query that can help me filter the sales by call duration?

pijushroy · Answer

Hi&nbsp;Anonymous&nbsp;You dont have Call Duration data in your Sale Table, so you put relationship on User Code. So user code filter the Sale Table and shows same Sale Value for both Call Duration for One UserSee details&nbsp;If you have User Code and Call Duration column in your Sale Table, please CONCATENATE User Code and Call Duration in both Call Table and Sale Table, after create relation on CONCATENATE column.Attached PBIXIf you have question, please feel free to let me know.If your requirement is solved, mark this comment as SOLUTIONIf this comment helps you, Please LIKE this comment / KUDOS

pijushroy · Answer

Hi&nbsp;Anonymous&nbsp;Simple way, you can change FIlter option from SINGLE SIDE to BOTH between user table and sales tableor&nbsp;you can change FIlter option from SINGLE SIDE to BOTH between user table and call tableWhen you are going to setup both way relationship, other indirect relationship may show error, turn off any of them for testing.If not works, please share sample pbix fileIf solve your requirement, please&nbsp;mark this answer as SOLUTIONIf this comment helps you, please&nbsp;LIKE this comment/Kudos&nbsp;

anonymous · Answer

PijushRoy&nbsp;&nbsp;PBIX&nbsp;I tried making the relationship between Users and Calls bidirectional and the visual changed but unfortunately it is still giving me innacurate results. What is happening is that for example the call duration 1-15, all the usernames has 1-15 as their call duration value so what happens is that in the sales table it will take the total sales for all the usernames. It's kinda difficult to explain but again example is the call duration &gt;60 only the username 16 has &gt;60 as its call duration value so in the sales table it would only take the total sales for the username 16.&nbsp;How do I make it so that it would show the correct results based on the Call Duration column

pijushroy · Answer

Anonymous&nbsp;Can you please create one pbix file with similar sample data and share

anonymous · Answer

PijushRoy&nbsp;&nbsp;Here is the pbix file

Forum Discussion

How can I make filtering work between these tables?

6 Replies

Related content

Filtered table

Can I filter the 1st fact table which then filters a 2nd fact table?

Filtering Table report

How to search specific word in table filter Calculations

Table visual filtering