Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello. In a table, I am trying to show the number of hours worked for each employee on the last day that the employee worked. For example, if employee Y worked 6 hours on 02/06/2020 and employee X worked 8 hours on 02/07/2020, I would like to show those hours within the table.
My current data model includes an employee table listing names and employee IDs and a fact table that has a date column, hours worked,. and an employee ID. I am currently creating a one way relationship between the employee table and fact table.
How can I accomplish this visualization using DAX?
Thank you.
Solved! Go to Solution.
Hi @Anonymous ,
I just create one sample PBIX file based on your case description, please find the details in this file.
If the above measure formula is not applicable in your scenario, please provide the related table structure and sample data.
Best Regards
Rena
Hi @Anonymous ,
I just create one sample PBIX file based on your case description, please find the details in this file.
If the above measure formula is not applicable in your scenario, please provide the related table structure and sample data.
Best Regards
Rena
Exactly what I was looking for. Thank you so much!
Please check if below measure could help you.
Measure = CALCULATE ( SUM ( factSales[Amount] ), FILTER ( ALL ( factSales ), factSales[Date] >= MAX ( Contract[active dates] ) && factSales[Date] <= MAX ( Contract[end dates] ) ) )
What is contract reffering to?
@Anonymous
Assuming you don't have date/calendar table, I would recommend you to create one. But it is not necessary to use the below DAX.
Measure = CALCULATE(SUM('Fact'[Hours]),FILTER('Fact','Fact'[Date]=MAX('Fact'[Date])))
Left table is the desired output with measure.. The right two tables are input data..
If it helps ,mark it as a solution
Kudos are nice too
Thank you for the reply. I do have this schema and have attempted the DAX formula, but did not get the result. Would there be any difference in the row context if the formula was instead:
CALCULATE(SUMX(VALUES(EMPLOYEE[NAME]),SUM(FACT[HOURS])),FILTER(FACT,FACT[DATE] = MAX(DATETABLE[DATE])))
@Anonymous
Why do you need sumx? Don't you have the relationship defined between employee and fact? Here is what i have for the model.
Considering date dimension..
CALCULATE(SUM('Fact'[Hours]),FILTER('Fact','Fact'[Date]=MAX('Date'[Date])))
Here is my Date table definition.
Date = CALENDAR(MIN('Fact'[Date]),MAX('Fact'[Date]))
If it helps, mark it as a solution
Kudos are nice too
Note that gerald is showing 0 hours on the left hand table, but I would like to capture Gerald's hours for Feb 6th.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |