topic Re: LOOKUPVALUE the right DAX? in DAX Commands and Tips

LOOKUPVALUE the right DAX?

ITManuel — Tue, 28 Apr 2020 14:02:05 GMT

Hi there,

i would like to analyze project and department related hours and costs from a data set from my ERP system. I have loaded two tables into my PBI file.

Table 1:

Project Department Employee Date Work process Hours

X X X X X X

Table 2:

Department Hourly rate

X X

I created a Matrix which shows the sum of hours for each project and department. Now I would like to calculate the costs for each department taking into consideration the hourly rates contained in table 2.

I tried the LOKKUPVALUE DAX as below, but can only use static search values such as "Engineering" for example.

HR = LOOKUPVALUE(Departments[Hourly rate];Departments[Department Name];"Engineering")

I would need variable search values to identify the hourly rates for each department and multiply it with the sum of hours in the matrix.

Can somebody help please?

Thanks

Re: LOOKUPVALUE the right DAX?

camargos88 — Tue, 28 Apr 2020 14:06:46 GMT

ITManuel ,

Try relating both tables and SUMX function.

So you can multiply the qty hours by hour rate in a measure.

Ricardo

Re: LOOKUPVALUE the right DAX?

Kogika — Tue, 28 Apr 2020 14:12:48 GMT

If you create a relationship One to Many on department, you can use the Related function within your SUMX expression to get the desired results.

Re: LOOKUPVALUE the right DAX?

ITManuel — Tue, 28 Apr 2020 14:29:28 GMT

Thanks for the quick responses.

Both tables are already related, but cannot solve it. I tried HR = sumx(Departments;RELATED( .....it will not propose any columns here)

I have multiple entries for each department in table 2, for example 3 for Engineering department. Will the sumx not result in 3 time the hourly rate of that department?

Thanks

Re: LOOKUPVALUE the right DAX?

camargos88 — Tue, 28 Apr 2020 14:30:30 GMT

ITManuel ,

Can you provide some sample data ?

Ricardo

Re: LOOKUPVALUE the right DAX?

ITManuel — Tue, 28 Apr 2020 15:27:58 GMT

Sorry I'm unable to upload files. How does it works?

Re: LOOKUPVALUE the right DAX?

camargos88 — Tue, 28 Apr 2020 15:38:56 GMT

ITManuel ,

You can paste the data here or upload the pbix using onedrive, googledrive, dropbox..

Ricardo

Re: LOOKUPVALUE the right DAX?

ITManuel — Tue, 28 Apr 2020 15:42:45 GMT

Can't past it here for some reason.

Here is a download link.

https://we.tl/t-J8LOOh4gDv

Thanks

Re: LOOKUPVALUE the right DAX?

Anonymous — Tue, 28 Apr 2020 17:20:19 GMT

// This is the simplest solution // but it can be slow if HourLedger // is big because RELATED is // doing a context transition for // each and every row it operates // on. // There's a relationship // Department[Department] 1:* HourLedger[Department]. // One-way filtering from the dimension to // the fact table. [Amount] = SUMX( HourLedger, HourLedger[Hours] * RELATED( Department[HourlyRate] ) ) // This one will be more performant [Total Hours] = SUM( HourLedger[Hours] ) [Amount] = SUMX( Department, [Total Hours] * Department[HourlyRate] ) // Bear in mind, though, that the column Department in HourLedger // should be hidden and no slicing by it should take // place. All slicing is always done through dimensions, // never directly on a fact table.

Best

Re: LOOKUPVALUE the right DAX?

ITManuel — Wed, 29 Apr 2020 07:40:46 GMT

Hi darlove,

both ways working perfectly.

Thank you very much, great help. 👍