cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jl20 Member
Member

Need DAX formula assistance!

Hi all,

 

I am trying to figure out a way to display hours worked and amount of work sold, by timekeeper, within the same line item on a visual.

 

Fact table has the following columns:

Matter #

Working timekeeper ID

Selling timekeeper ID

Hours Worked

Date Worked

 

It's possible but not required, for the working timekeeper to = selling timekeeper on any particular record. It's also possible that the person will only be displayed on one or the other. For simplicity, assume there are 100 timekeeper IDs. If John Doe's ID is 2033, and he worked on, but didn't sell a project, the record would reflect:

[matter #]=99999; [Working TKID] = 2033; [Selling TKID] = 1234

 

If he sold the job but didn't work on it:

[matter #]=99999; [Working TKID] = 4899; [Selling TKID] = 2033

 

I have a lookup table which includes following attributes (primary key is Working Timekeeper ID):

Employee Name

Employee Location

Employee Group

etc.

 

Ultimately I'm trying to figure out how to show the following on my report:

 

Employee Name        Sum of Hours [personally worked]        Sum of Hours [sold]

 

I am able to easily display the sum of hours worked (since it's the primary key), but I cannot figure out how to bring the second part (hours sold) onto the same row of the visual. Is there a way to temporarily replace Working timekeeper ID with Selling Timekeeper ID within the context of a measure?

 

Any advice would be greatly appreciated. Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
erik_tarnvik Established Member
Established Member

Re: Need DAX formula assistance!

It should work fine whether the timekeeper ID is numerical or text. The variable definition uses the MAX() function in order to extract the current timekeeper ID, you could just as well use MIN(), it will produce the same result. 

 

Regarding transformation of the table in Power Query, it is relatively straightforward. Just follow these steps:

 

1. Use "Get Data" and grab your "Facts" table, the one with these headings:

Matter #

Working timekeeper ID

Selling timekeeper ID

Hours Worked

Date Worked

2. Click "edit"

3. Rename this query "FactsWorked"

4. Remove the "Selling timekeeper ID" column.

5. Rename "Working timekeeper ID" to "Timekeeper ID"

6. Create a new custom column (Under the "add column" ribbon menu). Use "Activity" as the New Column Name and enter " = "WORKED" " as Custom Column Formula. Click OK.

7. Click "Recent Sources" and grab the same Facts table again.

8. Remove the "Working timekeeper ID" column.

9. Rename "Selling timekeeper ID" to "Timekeeper ID"

10. Create a new custom column (Under the "add column" ribbon menu). Use "Activity" as the New Column Name and enter " = "SOLD" " as Custom Column Formula. Click OK.

11. Click "Append Queries" under the "Home" ribbon. Choose your "FactsWorked" query. You should now have the result you need.

12. To clean up and avoid having "FactsWorked" actually show up in your models, right click on FactsWorked and Disable "Enable Load". This will make sure that you dont get this table into your model.

 

Now you can set up your relationship to the table where you have the timekeeper names and you have the primary relationship and your timekeeper ID in only one column.

 

In order to sum up the hours, you can define these measures and use in visuals:

HoursWorked = CALCULATE(SUM(Facts[Hours]), Facts[Activity] = "WORKED")
HoursSold = CALCULATE(SUM(Facts[Hours]), Facts[Activity] = "SOLD")

Hope this solves your challange!

 

 

 

 

 

 

 

 

 

 

View solution in original post

5 REPLIES 5
erik_tarnvik Established Member
Established Member

Re: Need DAX formula assistance!

In order to calculate hours sold for each working time keeper that has been "working", the following would work:

 

HoursSold = 
VAR 
    WID = MAX('Fact'[WorkID])
RETURN
  CALCULATE(SUM('Fact'[Hours]),FILTER(ALL('Fact'),'Fact'[SellID] = WID))

However, if you have timekeepers that only sell and haven't done any work, they would not appear with a zero for hours worked, they would just not appear at all and would not show up with whatever hours they have sold. My recommendation would be to use Power Query to restructure your data as follows:

 

Matter   Timekeeper ID       Activity           Hours
1            1234            SOLD               40
1            2033            WORKED             40

With the data structured in this fashion, arriving at your intended result becomes close to trivial.

Highlighted
jl20 Member
Member

Re: Need DAX formula assistance!

The initial solution did not work, unfortunately. I will try to restructure in power query, though I'm not sure if I can get it into that format given that there are many rows per matter, including the worker, period, and amounts.

jl20 Member
Member

Re: Need DAX formula assistance!

Is there a way to use variable if the timekeeper ID is text? That's what was causing to not to work. I don't want to change the data type in power query since the rest of the tables and reports are built off that text key.

erik_tarnvik Established Member
Established Member

Re: Need DAX formula assistance!

It should work fine whether the timekeeper ID is numerical or text. The variable definition uses the MAX() function in order to extract the current timekeeper ID, you could just as well use MIN(), it will produce the same result. 

 

Regarding transformation of the table in Power Query, it is relatively straightforward. Just follow these steps:

 

1. Use "Get Data" and grab your "Facts" table, the one with these headings:

Matter #

Working timekeeper ID

Selling timekeeper ID

Hours Worked

Date Worked

2. Click "edit"

3. Rename this query "FactsWorked"

4. Remove the "Selling timekeeper ID" column.

5. Rename "Working timekeeper ID" to "Timekeeper ID"

6. Create a new custom column (Under the "add column" ribbon menu). Use "Activity" as the New Column Name and enter " = "WORKED" " as Custom Column Formula. Click OK.

7. Click "Recent Sources" and grab the same Facts table again.

8. Remove the "Working timekeeper ID" column.

9. Rename "Selling timekeeper ID" to "Timekeeper ID"

10. Create a new custom column (Under the "add column" ribbon menu). Use "Activity" as the New Column Name and enter " = "SOLD" " as Custom Column Formula. Click OK.

11. Click "Append Queries" under the "Home" ribbon. Choose your "FactsWorked" query. You should now have the result you need.

12. To clean up and avoid having "FactsWorked" actually show up in your models, right click on FactsWorked and Disable "Enable Load". This will make sure that you dont get this table into your model.

 

Now you can set up your relationship to the table where you have the timekeeper names and you have the primary relationship and your timekeeper ID in only one column.

 

In order to sum up the hours, you can define these measures and use in visuals:

HoursWorked = CALCULATE(SUM(Facts[Hours]), Facts[Activity] = "WORKED")
HoursSold = CALCULATE(SUM(Facts[Hours]), Facts[Activity] = "SOLD")

Hope this solves your challange!

 

 

 

 

 

 

 

 

 

 

View solution in original post

jl20 Member
Member

Re: Need DAX formula assistance!

Thanks. I ended up solving the issue with the USERELATIONSHIP() function. Combining the data tables in power query as you suggested would have worked as well.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 59 members 1,121 guests
Please welcome our newest community members: