Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
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.
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
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... 😕
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
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)...?
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |