Duplicates in fact table

[piotr_pbi]

Hello,

please take a look into following fact table. The data is normally on Customer, Material, InvoiceNo, Date level.

After ETL processes there are SalesReps assigned to each line (basing on Customer, Product combination) which generates  duplicates in the fact table. In the real-life case there are some more hierarchy levels regarding SalesReps and therefore there might more than 1 duplicates for each combination - see R1 and R2).

An assumption has been made that for every original line every single SalesRep gets the same Quantity Sold assigned equally for his KPIs. The total flag indicates the unique row to be taken for the purpose of calculations on the level of detail upper than SalesRep and has been assigned in a RANDOM way. It is also important that the data can be filtered by all dimensions in the example as well as by single SalesRep.

My formula looks like this and does not need to use the TotalFlag at all:

VAR table1 =
ADDCOLUMNS (
SUMMARIZE (
data2,
data2[Customer],
data2[Product],
data2[InvoiceNo],
data2[Date]
),
"selectedvalue quantity", CALCULATE ( SELECTEDVALUE ( data2[QUANTITY SOLD] ) )
)

RETURN
SUMX ( table1, [selectedvalue quantity] )

which gives following reults:

but seems to have bad performance on the original dataset (around 100M rows in fact table).

Does anyone have any better solution?

[v-yadongf-msft]

Hi @piotr_pbi ,

Has your problem solved? If solved, please consider Accept it as the solution to help the other members find it more quickly.

[ValtteriN · Accepted solution]

Hi,

As a general rule in this sort of case I would think how to improve the ETL process if that causes this issue. As for a Dax Approach one tip that I have noticed is that MAX often has better results than SELECTEDVALUE in this sort of calculation.