Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Issue with Joins

Hi Experts

 

I have 2 tables Att( personid,matdistance,catergory) & clus(personid, Grpid, type(buyer/seller)), there should be 1:M from att to clus, due to many duplicates in att, created a table(lookup_att_clus) with distinct personid.

 

then i joined  lookup_att_clus to both tables and getting 1:M.

 

  now the problem is when I try to bring personid from all 3 tables i am getting an error "can't determine the relationship b/w the fields"

 

 

please help to resolve

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hello @San1979 ,

    I create a column in T1.

    Column = LOOKUPVALUE(T2[Cat],T2[personUuid],T1[PersonUuid])

    cat.PNG

    And then, create a visual table like this:

    cat2.PNG

    Therefore, the 313 records lost are "Cat - blank ()". But in T2, there is no "Cat - blank() option. The value "Total" is calculated on the basis of T1, not T2. You can create a measure as follows to correct the "Total" value:

    Measure = 
    CALCULATE (
        COUNT ( T1[GroupId] ),
        FILTER ( T1, T1[PersonUuid] IN VALUES ( T2[personUuid] ) )
    )

    cat3.PNG

    Best regards

    Icey

    If this post helps,then please consider accepting it as the solution to help other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    6 years ago

    @Icey Thanks for the solution.

    I just want to check, should the blanks be no match of personuuid b/w the 2 tables.

    but I think what are the personuuids present in T1 should be there in T2, then why their target

    cat is based on the condition

    Cat-SWITCH (
    TRUE (),
    t2[FR Dist]>-0 && t2[FR Dist] <-0.41, "Estudiante",
    t2[FR Dist]>0.41 && t2[FR Dist] <-0.45, "Otros")
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    But in T2, part of "personUuid" is not there. For example,

     

    T1:

     

    T2: no matched records.

     

    Best Regards,

    Icey

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    If you have to join A-B(1-M) and A-C.(1-M) Then you will not be able to take from table A, B, C all unsummarised Data. So either from B or C take first or last to Min/max value for the unsummarized attributes

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak ,

       

      my requirement is I need to take one column each from B and C

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Icey  Here is the link : Pbix File 

       

      From T1 table if I count(grpid) by course I got the correct split. but i take Cat from T2 and count(grpid) i am only getting 100 rest 313 is missing.

       

       

      hope you understand my issue.

      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        Hello @San1979 ,

        I create a column in T1.

        Column = LOOKUPVALUE(T2[Cat],T2[personUuid],T1[PersonUuid])

        cat.PNG

        And then, create a visual table like this:

        cat2.PNG

        Therefore, the 313 records lost are "Cat - blank ()". But in T2, there is no "Cat - blank() option. The value "Total" is calculated on the basis of T1, not T2. You can create a measure as follows to correct the "Total" value:

        Measure = 
    CALCULATE (
        COUNT ( T1[GroupId] ),
        FILTER ( T1, T1[PersonUuid] IN VALUES ( T2[personUuid] ) )
    )

        cat3.PNG

        Best regards

        Icey

        If this post helps,then please consider accepting it as the solution to help other members find it more quickly.