Forum Discussion
Need Help developing a DAx measure for a complicated Gantt Chart
Hello,
I have taken over responsibility for managing a reporting dashboard with multiple tabs. One of the tabs contains a Gantt chart. My responsibility is to combine all the Gantt charts from all the regions into one chart.
There is one measure that the person I am taking over from used to ensure only current "projects" are visual. This equation is confusing and, in the end, does not address all the variables that need to be addressed.
THE ORIGINAL CODE
# Responders for calendar =
var datefilter = ISFILTERED('Calendar'[Date])
var todaydate = DATEVALUE(VALUES('Refresh Time'[Data Last Refreshed]))
var nofilter = CALCULATE(
DISTINCTCOUNTNOBLANK('tblDeploy_DeploymentDetails - for calendar'[Employee ID]),
FILTER(
'tblDeploy_DeploymentDetails - for calendar',
OR(
'tblDeploy_DeploymentDetails - for calendar'[EndDate] >= todaydate,
ISBLANK('tblDeploy_DeploymentDetails - for calendar'[EndDate])
)
)
)
var withfilter = CALCULATE(
DISTINCTCOUNTNOBLANK('tblDeploy_DeploymentDetails - for calendar'[Employee ID]),
FILTER(
'tblDeploy_DeploymentDetails - for calendar',
OR(
AND('tblDeploy_DeploymentDetails - for calendar'[StartDate] <= MAX('Calendar'[Date]),
'tblDeploy_DeploymentDetails - for calendar'[EndDate] >= MIN('Calendar'[Date])),
AND('tblDeploy_DeploymentDetails - for calendar'[StartDate] <= MAX('Calendar'[Date]),
ISBLANK('tblDeploy_DeploymentDetails - for calendar'[EndDate]))
)
)
)
return
SWITCH(TRUE(),
datefilter, withfilter,
nofilter
)MY UPDATED CODE:
# Responders for calendar =
var datefilter = ISFILTERED('Calendar'[Date])
var todaydate = DATEVALUE(Today())
var nofilter = CALCULATE(
DISTINCTCOUNTNOBLANK('EMR_tblDeploy_DeploymentDetails (ALL)'[EmployeeID]),
FILTER(
'EMR_tblDeploy_DeploymentDetails (ALL)',
OR(
'EMR_tblDeploy_DeploymentDetails (ALL)'[EndDate] >= todaydate,
ISBLANK('EMR_tblDeploy_DeploymentDetails (ALL)'[EndDate])
)
)
)
var withfilter = CALCULATE(
DISTINCTCOUNTNOBLANK('EMR_tblDeploy_DeploymentDetails (ALL)'[EmployeeID]),
FILTER(
'EMR_tblDeploy_DeploymentDetails (ALL)',
OR(
AND('EMR_tblDeploy_DeploymentDetails (ALL)'[StartDate] <= MAX('Calendar'[Date]),
'EMR_tblDeploy_DeploymentDetails (ALL)'[EndDate] >= MIN('Calendar'[Date])),
AND('EMR_tblDeploy_DeploymentDetails (ALL)'[StartDate] <= MAX('Calendar'[Date]),
ISBLANK('EMR_tblDeploy_DeploymentDetails (ALL)'[EndDate]))
)
)
)
return
SWITCH(TRUE(),
datefilter, withfilter,
nofilter
)
Here are my questions:
1) How do I ensure that the date being used is the current date? Do I use 'Today()' or 'todaydate'?
2) The variables for the items to be seen are in the list below. I have tried multiple times to amend the code (my best version is above) to encompass all 4 variables, but I keep seeing numbers that include DUAL FUNCTIONS even though the code I wrote should eliminate them:
- Start date is less than or equal to current date
- End date is greater than or equal to end date
- Cancelled (true/false field) is false or null
- Dual Function (true/false field) is false or null
3)How can I create a simple DAX measure that provides a distinct count with filters for all the variables above? I am not sure how the more complicated DAX measure works, but It has to remain as is because otherwise the Gantt chart shows EVERYTHING, current or no.
Thank you for your help
Tammy
2 Replies
- amitchandak
Super User
tj7933 , try a measure like
Measure =
var _max = coalesce(max(Calendar[Date]), today())
return
CALCULATE(
DISTINCTCOUNTNOBLANK('EMR_tblDeploy_DeploymentDetails (ALL)'[EmployeeID]),,
FILTER(
'EMR_tblDeploy_DeploymentDetails (ALL)',
'EMR_tblDeploy_DeploymentDetails (ALL)'[StartDate] >= _max && (
isblank('EMR_tblDeploy_DeploymentDetails (ALL)'[EndDate]) ||
'EMR_tblDeploy_DeploymentDetails (ALL)'[EndDate] >= _max )
&& ISBLANK('EMR_tblDeploy_DeploymentDetails (ALL)'[EndDate])
)
)if the calendar is joined you need to use crossfilter refer
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970Also check
Matrix as Project plan Visual: https://youtu.be/R25QoiyoSVs
- tj7933Frequent Visitor
Sorry for the late reply.
"if the calendar is joined you need to use crossfilter refer"
How will I know if the calendar is "joined"?