Forum Discussion

KateEC's avatar
KateEC
Frequent Visitor
8 years ago

Extracting Duplicate Distinct Data

Hi All,

 

I'm not very good at eplaining so i'll attach a picture.

 

I am trying to extract duplicate data from one table and get it into a distinct list in another table.

 

I have a table with employees with a row for every date of holiday that they have booked. There is also a column giving the total number of future and past bookings per employee duplicated for every row with their name (Table A). I need to get this data into a single row for each distinct employee (Table B).

 

I have messed around with calculate, sum, average, filter, allexcept, earlier etc but I don't really understand them well enough to work this one out... I just keep getting lots of errors! :/

 

The background is two separate tables (related on employee name) - one with the leave dates booked and whether past, future etc (multiple rows per employee) and one with holiday allowance details per person (i.e. distinct list of employee names - one row per employee - with columns for various details such as remaining leave etc), but I need future/ past booking totals in the same table as the rest of the leave data (such as remaining leave, used leave).

 

Any help would be massively appreciated - a little out of my depth here :smileyfrustrated:

 

Thank you in advance!! 

 

Need Help

7 Replies

  • KateEC's avatar
    KateEC
    Frequent Visitor
    8 years ago

    Just to add, to get the Total Future/ Past, I used:

     

    =calculate(sum([Future Bookings]),filter(allexcept(leaveData, leaveData[Worker Name]),[Worker Name]=Earlier([Worker Name])))

     

    So i have tried variations of this desperately trying to work it out, but alas that has (unsurprisingly) been unsuccessful! 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi KateEC,

     

    To get TableB, please new a calculated table with below formula:

    TableB =
SUMMARIZE (
    TableA,
    TableA[Name],
    "Future Bookins", SUM ( TableA[Future] ),
    "Past Bookings", SUM ( TableA[Past] )
)

    Best regards,

    Yuliana Gu

    • KateEC's avatar
      KateEC
      Frequent Visitor
      8 years ago

      Thanks for your help - I'm not quite sure which columns the formula you've written is referring to on my example? I don't think I understand it enough to pick it apart... :/

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi KateEC,

         

        All column names being referred to in my formula are those in TableA you provided above. You just need to new a calculated table with my above DAX formula, then, you will get the output like TableB.

         

        Regards,

        Yuliana Gu

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    You should have a master list of all your employee names from both Tables (you can create that by appending the two tables, removing all other columns and then removing dulicates).  Then create a relationship between the two source Tables and the unique employee Table.  In your visual, drag the employee names from the unique employee table.  Lastly, write these 2 measures

     

    Measure1 = SUM(Data[Total Future])

    Measure2 = SUM(Data[Total Past])

     

    Hope this helps.