topic Re: Pivoting Data? in DAX Commands and Tips

Pivoting Data?

bob57 — Sun, 03 May 2020 19:25:11 GMT

Thanks in advance for your help. Wonderful forum for a rookie like me.

I need to create the following table visualization (Employee Hours/Status) based on the information provided further below.

Employee Hours/Status

Name	Week 1 Hours	Week 1 Status	Week 2 Hours	Week 2 Status
Joe	15	2	11	3
Bob	14	3	16	1
Sue	15	1	11	2

Over simplified example.

3 tables (Employees, TimeSheets, TimeEntries).

Relationships: Employees[ID] -> TimeSheets[Employees ID], TimeSheets[ID] -> TimeEntries[TimeSheets ID]

Summary of table data: Joe, Bob, and Sue have each worked for 2 days/week durings Weeks #1 and #2. Status #'s (1, 2, 3) map to "Draft, "Pending", and Approved" respectively (not necessarily relevent, just clarification).

Employees Table

ID	Name
1	Joe
2	Bob
3	Sue

TimeSheets Table

ID	Employee ID	Week
1	1	1
2	3	1
3	2	1
4	3	2
5	1	2
6	2	2

TimeEntries Table

ID	TimeSheets ID	Hours	Status
1	1	8	2
2	1	7	2
3	2	7	1
4	2	8	1
5	3	8	3
6	3	6	3
7	4	5	3
8	4	6	3
9	5	5	2
10	5	6	2
11	6	8	1
12	6	8	1

Re: Pivoting Data?

Anonymous — Sun, 03 May 2020 20:28:44 GMT

Hi bob57 ,

Is this what you want to visualise at the end, then a matrix can be helpful.

Thanks and Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Re: Pivoting Data?

bob57 — Sun, 03 May 2020 20:56:24 GMT

Thank you, but unfortunately, that does not satisfy the the boss' requirement. More specifically, I need 2 tables as they appear below. I only called for the one table in the original post to keep things simple. Also, the "status" does need to remain numeric because, ultimately, they will be displayed using a traffic light KPI. But I'm not worried about that. I just need these two tables.

Hours by Week

Name	Week 1 Hours	Week 2 Hours
Joe	15	11
Bob	14	16
Sue	15	11

Status by Week

Name	Week 1 Status	Week 2 Status
Joe	2	3
Bob	3	1
Sue	1	2

Re: Pivoting Data?

Anonymous — Sun, 03 May 2020 21:19:34 GMT

Hi bob57 ,

This can again be done through 2 matrix visuals.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Re: Pivoting Data?

bob57 — Mon, 04 May 2020 00:24:53 GMT

That worked perfectly. Thank you for your effort and prompt response.