Forum Discussion

PatrickLamoste's avatar
PatrickLamoste
Icon for Helper I rankHelper I
7 years ago
Solved

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

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

 

EIDENTRY DATEHOURS
11111101/01/20192
11111105/01/20191
11111106/01/20192
11111107/01/20193
11111108/01/20194
11111109/01/20192

 

Table 2 

 

EIDDEPARTMENTEFFECTIVE DATEEND DATE
111111HR01/01/201905/01/2019
111111FINANCE06/01/2019 

 

Thank you,

 

Patrick

Need Help
Tips and Tricks
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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