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.
I have 10 employees in my Dept. Each one submits his leave plan begining of a year which includes Emp No, Leave Start and Leave end dates (Emp Table). I want to see on a selected date if a particular employee is available or not using calculated column as given below;
Emp 1 =
VAR _Check= SELECTEDVALUE(Emp No)
VAR _max = SELECTEDVALUE('Date'[Date])
VAR _min = SELECTEDVALUE('Date'[Date])
RETURN
IF (_Check= "Emp 1" && _min>=SELECTEDVALUE(Emp[Leave Start]) && _max<=SELECTEDVALUE(Emp[Leave End]),"Leave","Available")
I am getting "Available" on all dates. Let me know what is wrong in above !
Hi,
Below works;
__Available =
var _selDate = SELECTEDVALUE('Date'[Date])
return
if (
COUNTROWS(FILTER('Emp Table', 'Emp Table'[Leave Start] >= _selDate && _selDate <= 'Emp Table'[Leave End] )) > 0 , "Not Available", "Available")
As seen here:
Please note the data model and the helper columns to join, value is 1 in both.
Please mark as solution if so. Thumps up for the effort is appreciated.
Kind regards,
Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |