CODEWITHSUNDEEP
tableau-desktop
TheReddsable
TheReddsable

Reputation: 67

Tableau Duplicate Values after LEFT JOIN

I am fairly new to Tableau and have some issues regarding duplication of data after joining.

So let's say I have fact table 'Transactions' that has a level of granularity based on transaction number. Each transaction has a Sales Person ID. I get the data of Sales Persons from another table by left joining my Primary Fact table to the Dimension Table.

Before I join by data I get a number of 1,200 transactions (number of records or Count(Transaction_number). After I join it, I get 1,300 transactions. This happens because in my dimension table there is one Sales Person ID that has 2 names and 2 separate rows basically.

How can I write a formula that will count the real number of transactions? I want basically Tableau to join the Sales Person only once, even if in the dim table it has 2 separate values on 2 separate rows.

I tried

{FIXED:[Transaction_number]:MIN(Sales_Person_ID)}

but that didn't give the wanted result.

Any thoughts?

Upvotes: 0

Views: 1243

Answers (1)

Fosstin
Fosstin

Reputation: 141

The root issue is that your Sales Person table is not normalized. Normalize your data so there is only one name per ID. If you choose not to do this, you will likely have to make repeated accommodations for these duplicates as you build different visualizations. Assuming the [Transaction_number] field contains only unique values, you can just COUNT([Transaction_number]) to get a count of all the transactions. Otherwise, use COUNTD() to get a distinct count of transaction numbers.

The calculated field logic you provided will be interpreted by Tableau Desktop as, "For each record in Transaction_number, return the lowest Sales_Person_ID." Adding this to a sheet will give you a list of Sales Person IDs, but you want a count of transactions.

You don't even need to write a formula. Just drag [Transaction_number] to the sheet canvas, right click this [Transaction_number] pill and change the Measure aggregation from SUM() to either COUNT() or COUNTD().

Upvotes: 1

Related Questions