topic Re: Employee Available report in DAX Commands and Tips

Employee Available report

Moha_shanti — Thu, 21 Dec 2023 04:41:16 GMT

I have 2 tables

1st table has 3 columns are the employee ID, Month and available hours.
2nd table has alos employee ID, Month, work orders acutal hours and more data.

the key record in both table is employee ID

I want to create a visual table having Available hours ans Work order actual hours per employee and month, like below

Month	Employee ID	Work order actual hours	Available Hours
October	123456	100	20
November	321654	50	10

Re: Employee Available report

123abc — Thu, 21 Dec 2023 05:24:54 GMT

To create a visual table in Power BI that shows both available hours and work order actual hours per employee and month, you'll need to perform a series of steps that involve merging the two tables and then creating a table visualization. Here's a step-by-step guide:

Import Data:
- Ensure you've imported both tables into Power BI.
Relationships:
- Make sure there's a relationship established between the two tables based on the Employee ID and Month columns.
Merge Queries:
- Go to the Home tab in the Power BI Desktop.
- Click on Edit Queries to open the Power Query Editor.
- Select the first table, then choose Merge Queries.
- In the dialog box, choose the second table and select the matching columns (Employee ID and Month).
- Ensure you select a type of join that retains all rows from both tables. This is important because you want to ensure you capture all available hours and work order actual hours.
Expand the Merged Table:
- After merging, you'll see a new column that contains a table. Click on the double-arrow icon at the column header to expand it.
- Select the columns you want to keep: Employee ID, Month, Available hours, and Work order actual hours.
Create a Table Visualization:
- Once back in the main Power BI window, click on the Insert tab.
- Choose Table from the Visualization pane to add a new table visualization to your report canvas.
- Drag and drop the fields (Month, Employee ID, Work order actual hours, and Available hours) into the table visualization.
Format the Table (Optional):
- You can further format the table by adjusting column headers, resizing columns, or applying custom styles as per your requirements.
Filter (Optional):
- If you want to filter the data by a specific month or any other criteria, you can use slicers or filters available in Power BI.
Refresh Data:
- Ensure that your data refresh settings are correctly configured if you need the report to update automatically with new data.

By following these steps, you should be able to create a visual table in Power BI that displays both available hours and work order actual hours per employee and month based on the two tables you have.

Re: Employee Available report

v-jincheng-msft — Fri, 22 Dec 2023 02:13:04 GMT

Hi Moha_shanti ,

Thanks for the information you have given.

Please try the add Collum operation on 1st Table using the following DAX after checking the table name and column name:

Work order actual hours = SUMX(FILTER(all('Table_2'),'Table_2'[employee ID]=EARLIER(Table_1[employee ID])),'Table_2'[Work order actual hours])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Joseph Ji

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.