Forum Discussion

RobertGilbert's avatar
RobertGilbert
Regular Visitor
8 years ago
Solved

Table Joins

I have a table of unique values (combined key of customer and material), no other values in that table. I have sales table with that key also, but obviously not unique.  There are about 12 million ro...
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi RobertGilbert

     

    I understand you are sure that the values in dim table is unique. Can you try create a new calculated table and try joining this with your fact table for testing purpose? ( Go to Modelling -> New Table-> Use this formula)

     

    Table =
    FILTER (
        DISTINCT (
            SELECTCOLUMNS ( CustomerTable, KeyColumn, CustomerTable[KeyColumn]  )
        ),
        ISBLANK ( CustomerTable[KeyColumn]) = FALSE ()
    )

     

    Thanks
    Raj

RobertGilbert's avatar
RobertGilbert
Regular Visitor
8 years ago

I believe you are still not quite understanding me.  I know that the customer/material file, which contains 1 field is unique.  There are 3,356,863 entries in this file.  I am trying to join it with our sales data file, which has the same key and has about 12,000,000 entries.  Now, you may ask why combine a file with one field to another file which contains that same field.  The truth is, there should be other fields in the customer/material file, however, until I can get this combine issue resolved, I am not placing them there.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi RobertGilbert

 

I understand you are sure that the values in dim table is unique. Can you try create a new calculated table and try joining this with your fact table for testing purpose? ( Go to Modelling -> New Table-> Use this formula)

 

Table =
FILTER (
    DISTINCT (
        SELECTCOLUMNS ( CustomerTable, KeyColumn, CustomerTable[KeyColumn]  )
    ),
    ISBLANK ( CustomerTable[KeyColumn]) = FALSE ()
)

 

Thanks
Raj

  • RobertGilbert's avatar
    RobertGilbert
    Regular Visitor
    8 years ago

    Rajen,  This process worked, and also uncovered the underlying issue.  I have many entries that are 'near' duplicates.  Where the first character is a 'space' or not, and the rest is the same, example " 12345" and "12345".  Now, even doing a 'trim' and 'remove duplicates' did not resolve, however your neat DAX trick below with creating new tables did.  I was given the clue as to what the issue was, when I saw that these records were missing, so now only "12345" rather than both.

     

    Thanks Much

    Bob Gilbert