cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wverheijen Frequent Visitor
Frequent Visitor

Fetch value from column by category

Hi! I have a worktime-table which specifies per employee how many hours they work on each day according to their countract:

employeehoursonmondayhoursontuesdayhoursonwednesdayhoursonthursdayhoursonfridayhoursonsaturdayhoursonsunday
10000800
28888000
30888000
48888800

 

I want to compare this to the actual hours they register, to see which employees haven't  fully registered their contracted hours.

 

First, I created a date table and added a weekday function, so monday = 1 and sunday = 7. Second, I created a measure in an if-else statement:

Contracthours = 
IF([dayofweek] = 1; MAX(WORKTIME[HOURSONMONDAY]);
    IF([dayofweek] = 2; MAX(BS_WORKTIME[HOURSONTUESDAY]);
        IF([dayofweek] = 3; MAX(WORKTIME[HOURSONWEDNESDAY]);
            IF([dayofweek] = 4; MAX(WORKTIME[HOURSONTHURSDAY]);
                IF([dayofweek] = 5; MAX(WORKTIME[HOURSONFRIDAY]);
                    IF([dayofweek] = 6; MAX(WORKTIME[HOURSONSATURDAY]);
                        IF([dayofweek] = 7; MAX(WORKTIME[HOURSONSUNDAY]);0)
                      )
                  )
              )
          )
      )
  )   

Now, this results  beautifully in a date table which finds the day of the week and returns the maximum number of hours there is worked on a day. Now, when I add the employee names, no contract hours are shown:

 

datedayofweekemployeecontracthours 
1-10-201811null(should be 0)
1-10-201812null(should be 😎
1-10-201813null(should be 0)
1-10-201814null(should be 😎
2-10-201821null(should be 0)
2-10-201822null(should be 😎
2-10-201823null(should be 😎
2-10-201824null(should be 😎

 

If the contract hours would display here, i would be able to compare these to the registered numbers (or if there are none!) and my goal is reached.

 

Please help!

 

Regards, Wessel

1 ACCEPTED SOLUTION

Accepted Solutions
wverheijen Frequent Visitor
Frequent Visitor

Re: Fetch value from column by category

Fixed it!

 

The problem was that the relationship between timesheetentry and employees in my original table was set to "double" while it's supposed to be "single". This was all.

 

@v-piga-msft big thanks for your help and effort!

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: Fetch value from column by category

Hi @wverheijen,

 

It seems that you create another table date table and the weekday function, but do you create the relationship with the worktime-table?

 

If not, please create the relationship between the tables with employee.

 

Then please modify your the formula like below.

 

Contracthours = 
IF([dayofweek] = 1; RELATED(WORKTIME[hoursonmonday]);
    IF([dayofweek] = 2; RELATED(WORKTIME[hoursontuesday]);
        IF([dayofweek] = 3; RELATED(WORKTIME[hoursonwednesday]);
            IF([dayofweek] = 4; RELATED(WORKTIME[hoursonthursday]);
                IF([dayofweek] = 5; RELATED(WORKTIME[hoursonfriday]);
                    IF([dayofweek] = 6; RELATED(WORKTIME[hoursonsaturday]);
                        IF([dayofweek] = 7; RELATED(WORKTIME[hoursonsunday]);0)
                      )
                  )
              )
          )
      )
  )   

Here is your desired output.

 

Capture.PNG

 

In addition, you could refer to my attachment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
wverheijen Frequent Visitor
Frequent Visitor

Re: Fetch value from column by category

Thank you for your reply!

 

I get an error message that there's no correct context between [dayofweek] and my worktime table. The relation is as following:

 

DATES 1---* TIMESHEETENTRY *---1 EMPLOYEES 1---* WORKTIME

 

The timesheetentry table holds the hours that employees register per date. So it's structured as: employee/date/hours/hoursdescription.

 

right now my table returns only the employees that have values in the timesheetentry table on a specific date.

I think the contracthour function must calculate the hours dat employees are supposed to work on a specific date, even when they've not registered any hours.

 

In SQL I would resolve this by using a LEFT JOIN in the from clause between dates, timesheetentry and employees, to show all dates where employees are supposed to work the contracted hours from the worktime table.

Community Support Team
Community Support Team

Re: Fetch value from column by category

Hi @wverheijen,

 

If it is convenient, could you share a dummy pbix file which can reproduce your scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
wverheijen Frequent Visitor
Frequent Visitor

Re: Fetch value from column by category

Hi cherry,

 

I was working on duplicating a dummy report for you in the same structure as my original table. When replicating my table, i suddenly got the result I'm looking for.

 

So if I recreate my table to a dummy, it works nicely, but my original report in thes same structure doesn't. I've tracked a single user ID and it should appear exactly the same.

 

Any thoughts?

 

You can look into the report here: https://1drv.ms/u/s!Aq8v0AAEkXxvgapoGfLv0j6q61FncA

wverheijen Frequent Visitor
Frequent Visitor

Re: Fetch value from column by category

Fixed it!

 

The problem was that the relationship between timesheetentry and employees in my original table was set to "double" while it's supposed to be "single". This was all.

 

@v-piga-msft big thanks for your help and effort!

View solution in original post

Community Support Team
Community Support Team

Re: Fetch value from column by category

Hi @wverheijen,

 

You're welcome! It's glad that you have solved your problem.Smiley Very Happy

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,332)