Extracting Duplicate Distinct Data

[KateEC]

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 

Thank you in advance!! 

[Ashish_Mathur]

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.

[v-yulgu-msft]

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]

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]

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

[KateEC]

Hi - thanks for that @v-yulgu-msft - misread your column names there!

Sorry for another silly question - but how do I create a calculated table - i've read somewhere that this feature isn't available in Excel 2016 (which I'm using)...?

[v-yulgu-msft]

Hi @KateEC,

The solution I posted above applied to Power BI desktop. If you want to analyze data in Excel tool, please post your question on Excel forum for more advice.

Regards,

Yuliana Gu

[KateEC]

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!