Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good evening all,
I am new in DAX code. Can you share with me DAX code to show total staff counts result if total working hours staffs are more than Total Man days hours in Table 2. My expected result is Jan - 2 , Feb - 2
Appreciated, thanks.
Table 1
Staff Total Working Hours | |||
Staff | Month | Week | Total Working Hours |
A | Jan | 1 | 40 |
A | Jan | 2 | 35 |
A | Jan | 3 | 37 |
A | Jan | 4 | 40 |
A | Jan | 5 | 30 |
B | Jan | 1 | 38 |
B | Jan | 2 | 39 |
B | Jan | 3 | 40 |
B | Jan | 4 | 30 |
B | Jan | 5 | 30 |
C | Jan | 1 | 40 |
C | Jan | 2 | 40 |
C | Jan | 3 | 40 |
C | Jan | 4 | 40 |
C | Jan | 5 | 30 |
A | Feb | 1 | 35 |
A | Feb | 2 | 40 |
A | Feb | 3 | 40 |
A | Feb | 4 | 30 |
B | Feb | 1 | 40 |
B | Feb | 2 | 35 |
B | Feb | 3 | 30 |
B | Feb | 4 | 25 |
C | Feb | 1 | 30 |
C | Feb | 2 | 30 |
C | Feb | 3 | 32 |
C | Feb | 4 | 35 |
Table 2
Total Man days hours | |
Jan | 180 |
Feb | 130 |
Hi @Anonymous
first, create a new calculated table
Table =
ADDCOLUMNS(
SUMMARIZE(
'Table 1',
'Table 1'[Month],'Table 1'[Staff],
"Total hours",SUM('Table 1'[Total Working Hours])
),
"Man days hours",IF(CALCULATE(MAX('Table 2'[Total Man days hours]),'Table 2'[Month]=EARLIER([Month])) <= [Total hours],1,0)
)
then put it in visual
Thanks @az38 for your reply.
When I write the code until XXX, the system only show the functions instead of showing the column name, how should I continue from here? Thanks.
@Anonymous
pay attention, you forgot closing parenthesys ")") after MAX sentence
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |