cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Many to many relationship - Type 2 data from CSV

Spoiler
 

Hi All, 

 

Im facing the issue creating an appropiate data model for my 2 report requirements. The 2 CSV files are is like the following 

format.

 

Sales   
Sales IDCustomer Sale AmountDate
11103/1/18
211201/1/18
32113/1/18
43143/1/18
5315

2/1/18

 

 

Field Details   
CustomerEmployeeValid From Valid toValid record
1John Doe 1 2/16/1812/31/99TRUE
2John Doe 22/16/1812/31/99TRUE
3John Doe 32/16/1812/31/99TRUE
1John Doe 212/31/172/15/18FALSE
3John Doe 212/31/172/15/18FALSE
4John Doe 1 2/16/1812/31/99TRUE
5John Doe 32/16/1812/31/99TRUE
6John Doe 22/16/1812/31/99TRUE

 

I need to create 2 visuals which allows me to following: 

1. Total Sale amount made by each Employee (For Example: In case of John Doe 1 this is 10 and John Doe 2 is 146)

2. matrix to show past and future sales including new opportunities for sale per Employee. (For Example: for John Doe 1 -  This is should show sales with Sales ID 1,2 as well as opportunity for 4)

 

Any Help would be greatly appreciated.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Many to many relationship - Type 2 data from CSV

Also, Thank you to the community,

 

I was able to find an answer somewhere else, but anyone is still looking for the answer here,

 I created a calculated column "Employee_Sold" and used 2 variables to join and find the answer

 

Employee_Sold =>

var thisdate = SALES[date] var thisterritory = SALES[customer] return CALCULATE(FIRSTNONBLANK(FieldDetails[Employee],0),FILTER(all(FieldDetails),FieldDetails[Valid from] < thisdate && thisdate <FieldDetails[Valid to] && thisterritory = FieldDetails[customer]))

11 REPLIES 11
Super User
Super User

Re: Many to many relationship - Type 2 data from CSV

Why is 4 an opportunity?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Re: Many to many relationship - Type 2 data from CSV

Customer 4 is an opportunity, as John Doe 1 not made a sale for it. 

Super User
Super User

Re: Many to many relationship - Type 2 data from CSV

OK, great, but what in the data says that? I don't see it.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Re: Many to many relationship - Type 2 data from CSV

Hi Greg,

As this record is present in field details table but a sales entry is missing.

my major concern is how can relate the two data sources which have many to many relationship and solve the issue of issue of where I can use one slicer and be ae to select all past and present sales made for one customer by multiple employees

Super User
Super User

Re: Many to many relationship - Type 2 data from CSV

To solve many-to-many, create a bridge table of unique ID's and relate that table to both of your other tables. Use your bridge table in your slicer.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Many to many relationship - Type 2 data from CSV

Hi,

 

You should always explain the result.  Why is the asnwer against John Doe 2 131?

Re: Many to many relationship - Type 2 data from CSV

@Ashish_Mathur the total for John Doe 2 should be 131 because he has made 2 sales in total. One for Customer 1 for 120 and one for Customer 2 for 11 which totals to 131

Super User
Super User

Re: Many to many relationship - Type 2 data from CSV

Hi,

 

Why should you not consider Customer 3 for John Doe 2?  There are 2 dates coresponding to customer 3 in Table 1 - I Feb 2018 and 1 March 2018.  1 Feb 2018 falls in the date range specified in Table 2 for Customer 3.

Re: Many to many relationship - Type 2 data from CSV

Sorry, My Bad. It includes Customer 3

 

the total sale  will be for John Doe 2 146