Forum Discussion

NipponSahore's avatar
NipponSahore
Icon for Resolver II rankResolver II
8 years ago
Solved

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 ID Cu...
Need Help
  • NipponSahore's avatar
    NipponSahore
    8 years ago

    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]))

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

Why is 4 an opportunity?

  • NipponSahore's avatar
    NipponSahore
    Icon for Resolver II rankResolver II
    8 years ago

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

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      8 years ago

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

      • NipponSahore's avatar
        NipponSahore
        Icon for Resolver II rankResolver II
        8 years ago
        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