I'm getting duplicated rows when table relationships are established...

[PatrickLamoste]

Hi,

I am currently working on 2 tables the first table has an employees total hours for the day and the second table is the department details of the employee. When I establish a relationship and combine them with my date dim I get repeated rows.

I would really appreciate your help on this.

Table 1

EID	ENTRY DATE	HOURS
111111	01/01/2019	2
111111	05/01/2019	1
111111	06/01/2019	2
111111	07/01/2019	3
111111	08/01/2019	4
111111	09/01/2019	2

Table 2 

EID	DEPARTMENT	EFFECTIVE DATE	END DATE
111111	HR	01/01/2019	05/01/2019
111111	FINANCE	06/01/2019

Thank you,

Patrick

[v-lili6-msft · Accepted solution]

hi, @PatrickLamoste 

Since your two table is a many to many relationship, and there is no [DEPARTMENT] filed in table1, so it will lead to this result.

And from your sample data, I think this your expected output.

Create a measure instead of drag [Hours] into visual directly.

Measure = 
CALCULATE (
    SUM ( Table1[HOURS] ),
    FILTER (
        Table1,
        Table1[ENTRY DATE] >= MIN ( Table2[EFFECTIVE DATE] )
            && Table1[ENTRY DATE]
                <= IF (
                    MAX ( Table2[END DATE] ) = BLANK (),
                    DATE ( 9999, 12, 31 ),
                    MAX ( Table2[END DATE] )
                )
    )
)

 Result:

and here is sample pbix file, please try it.

Best Regards,

Lin

[PatrickLamoste]

Thank you! This works perfectly.