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
