Need DAX formula assistance!

[jl20]

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!

[erik_tarnvik]

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.

[jl20]

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]

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 · Accepted solution]

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!

[jl20]

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.