Forum Discussion

RobertGilbert's avatar
RobertGilbert
Regular Visitor
7 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
    7 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

Anonymous's avatar
Anonymous
Not applicable
7 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
7 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