topic Re: DAX Formula review only (Overlapping Start and End Dates by Roles and their specialisation) in DAX Commands and Tips

DAX Formula review only (Overlapping Start and End Dates by Roles and their specialisation)

Deevo_ — Mon, 28 Oct 2024 07:00:48 GMT

Hi All,

Sorry I cannot provide sample data. Can someone please help me review the structure of this dax measure?

Background info:

2 Data sources
- Project costings (not in flight) have roles and specialisation combos and each combo has a work start and end date
- Projects in flight also have roles and specialisation combos and each combo has a work start and end date

What the measure is suppose to do:

From a single selection list, a "project costing code" is selected.
The roles and specialisations within the "project costing code" are compared to the exact same roles and specialisations combos of "projects that are currently in flight" AND have overlapping work start and end dates.

How this measure is applied in the visual:

Apply this measure on the table filter and apply "is not blank".

The expected result

Return "projects that are currently in flight" AND have overlapping work start and end dates for the exact same roles and specialisation combo as the "Project costing".

DAX Measure:

Project Costing Has Role overlap with In Flight Projects =

VAR SelectedCosting = SELECTEDVALUE('Projects (Costings)'[CostingDisplayName])

VAR SelectedCostingStart = SELECTEDVALUE('Projects (Costings)'[CostingStartDate].[Date])

VAR SelectedCostingFinish = SELECTEDVALUE('Projects (Costings)'[CostingFinishDate].[Date])

VAR InFlightProjectStart = SELECTEDVALUE('Projects (In flight)'[InFlightStartDate].[Date])

VAR InFlightProjectFinish = SELECTEDVALUE('Projects (In flight)'[InFlightFinishDate].[Date])

VAR SelectedCostingRole = SELECTEDVALUE('Projects (Costings)'[RoleName])

VAR SelectedCostingSpecialisation = SELECTEDVALUE('Projects (Costings)'[SpecialisationName])

VAR InFlightProjectRole = SELECTEDVALUE('Projects (In flight)'[RoleName])

VAR InFlightProjectSpecialisation = SELECTEDVALUE('Projects (In flight)'[SpecialisationName])

VAR HasOverlap =

(

((InFlightProjectFinish <= SelectedCostingFinish && InFlightProjectFinish >= SelectedCostingStart) &&

(InFlightProjectRole = SelectedCostingRole && InFlightProjectSpecialisation = SelectedCostingSpecialisation))

((InFlightProjectStart >= SelectedCostingStart && InFlightProjectStart <= SelectedCostingFinish) &&

(InFlightProjectRole = SelectedCostingRole && InFlightProjectSpecialisation = SelectedCostingSpecialisation))

)

RETURN

IF(HASONEVALUE('Projects (Costings)'[CostingDisplayName]) && HasOverlap,"Overlap",BLANK())

Re: DAX Formula review only (Overlapping Start and End Dates by Roles and their specialisation)

lbendlin — Mon, 28 Oct 2024 12:43:18 GMT

Yes, you can provide sample data. Make an effort.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: DAX Formula review only (Overlapping Start and End Dates by Roles and their specialisation)

Anonymous — Tue, 29 Oct 2024 06:16:34 GMT

Hi Deevo_ ,

I have checked your code, and I am confused about how did you create relationship between two tables.

As far as I know, there could only one active relationship between two tables.
However, you need to use the projectname and role name to determine the start date and end date values.

I think selectvalue() for data in Projects (In flight) will not work.

I think you need to calculate the data with more filters like Projects (Costings)[Name] = Projects (In flight)[Name] and Projects (Costings)[Role] = Projects (In flight)[Role]. Then compare the dates in two tables.

If this reply still couldn't help you solve your issue, please share a easy sample file with us and show us a screenshot with the result you want.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX Formula review only (Overlapping Start and End Dates by Roles and their specialisation)

Deevo_ — Fri, 01 Nov 2024 05:21:50 GMT

Hi Rico, Thanks for your reply. Sorry I am on a work computer and they have restrictions on personal drives (google drive, dropbox). I did create a file but now i cannot upload it.

Thank you for providing me with some ideas. I re-wrote one section of my DAX. I believe I now have what I need 🙂

From this:

VAR HasOverlap =

(

((InFlightProjectFinish <= SelectedCostingFinish && InFlightProjectFinish >= SelectedCostingStart) &&

(InFlightProjectRole = SelectedCostingRole && InFlightProjectSpecialisation = SelectedCostingSpecialisation))

((InFlightProjectStart >= SelectedCostingStart && InFlightProjectStart <= SelectedCostingFinish) &&

(InFlightProjectRole = SelectedCostingRole && InFlightProjectSpecialisation = SelectedCostingSpecialisation))

)

To This:

VAR HasOverlap =

(InFlightProjectRole = SelectedCostingRole && InFlightProjectSpecialisation = SelectedCostingSpecialisation)

(

(InFlightProjectFinish <= SelectedCostingFinish && InFlightProjectFinish >= SelectedCostingStart)

(InFlightProjectStart >= SelectedCostingStart && InFlightProjectStart <= SelectedCostingFinish)

)