stand-alone measure giving improper calculations

[DK_Bee]

Hello,

I have been scratching my head for days and looking for information that shed light of for the issues I am facing. For further clarification this information is related to a construction project schedule.

Issue 1: Improper Calculation

I have the following stand alone measures that calculates the following values from a table range of data

• Start Dat := MIN('DCMA Summary'[Start]) - Finds the minimum date from all the rows from the different activities' start dates

• Finish Dat := MAX('DCMA Summary'[Finish])- Finds the max date from all the rows from the different activities' finish dates

I used a 3rd measure to find the project Duration

• Project Duration := NETWORKDAYS([Start Dat],[Finish Dat],1,ALLSELECTED(Holidays[Date]))

When I try and create a Calculated Column Measure that checks whether individual rows containting Original Duration satisfies the following expression, it doesn't calculate properly (original duration divide by project duration is more that 0.1)

• Level of Detail = DIVIDE('DCMA Summary'[Original Duration],[Project Duration])>0.1

i.e in some cases if the original duratio is 11 divided by project duration of 97 it correctly calculates as 0.11 =  True. But with lower values like an original duration of 3, it incorrectly calculates 3/97 = 1 =True.  (Note: I removed the '>0.1' in an attempt to troubleshoot). It is correct for Original duratio of 0/97 = 0 = False

Could you please help me fix this?

Issue 2: Filtering Data using a Standalone Measure

I was able to create several meaures that calculate the different quarters within the project duration. Some were a build up of the measures above.

• Q1 Start = [Start Dat]
• Q1 Finish = [Q1 Start]+(([Finish Dat]-[Start Dat])/4)
• Q1 Duration = NETWORKDAYS([Q1 Start],[Q1 Finish],1,ALLSELECTED(Holidays[Date]))

I have data for several projects that was imported into power BI. Each project has a unique Project ID with various activities. Within my report interface I use a filter to show one project at a time. I am trying to tag all the activities regardless of project with a dynamic identifier of which Quarter it belongs to (Q1, Q2, Q3,Q4) but when I use the formula below in the table view, I get the error below

• Q1 Identifier = IF('DCMA Summary'[Finish]<=[Q1 Finish], "Q1")

A circular dependency was detected: DCMA Summary[Level of Detail], DCMA Summary[Q1 Identifier], DCMA Summary[Level of Detail].

How can I dynamically create an expression that is able to tag the correct Quarter to, each activity undertaken at different points for all the different projects in my data base?

  

Issue 3: Stand alone Formula conversion into a table

Is there any way to move or create my stand alone meaure as a table, since some filter functions are only able to filter data that is within a table. Or is there a way to express the syntax in a way that makes the DAX formula select the meaure as the entity to filter by? I would like to filter data by the different quarters.

[Anonymous · Accepted solution]

Hi @DK_Bee 

Here is my solution, hope it helps. Please refer to the uploaded pbix file for full details.

Issue 1: Improper Calculation

Change your “Project Duration” and "Level of Detail" measures with the following DAX:

Project Duration = 
CALCULATE(
    NETWORKDAYS(
        MIN('DCMA Summary'[Start]),
        MAX('DCMA Summary'[Finish]),
        1,
        ALLSELECTED(Holidays[Date])
    )
)

Level of Detail = 
DIVIDE(
    'DCMA Summary'[Original Duration],
    CALCULATE(
        [Project Duration],
        REMOVEFILTERS('DCMA Summary'[Quarter Identifier]),
        ALLSELECTED('DCMA Summary'[Activity ID])
    )
) > 0.1

Issue 2: Filtering Data using a Standalone Measure

1. Create a new table with the following DAX:

Quarters = 
SELECTCOLUMNS(
    CROSSJOIN(
        VALUES('DCMA Summary'[Project]),
        {"Q1", "Q2", "Q3", "Q4"}
    ),
    "Project", [Project],
    "Quarter", [Value],
    "Start Date", 
            VAR ProjectStart = CALCULATE(MIN('DCMA Summary'[Start]), ALLEXCEPT('DCMA Summary', 'DCMA Summary'[Project]))
            RETURN ProjectStart,
        "Finish Date", 
            VAR ProjectFinish = CALCULATE(MAX('DCMA Summary'[Finish]), ALLEXCEPT('DCMA Summary', 'DCMA Summary'[Project]))
            RETURN ProjectFinish
)

2. Create a new column in the new table to calculate each quarter finish date

Quarter Finish Date = 
VAR StartDate = Quarters[Start Date]
VAR FinishDate = Quarters[Finish Date]
VAR Q1Finish = StartDate + (FinishDate - StartDate) / 4
VAR Q2Finish = Q1Finish + 1 + (FinishDate - StartDate) / 4
VAR Q3Finish = Q2Finish + 1 + (FinishDate - StartDate) / 4
VAR Q4Finish = FinishDate
RETURN
    SWITCH(
        'Quarters'[Quarter],
        "Q1", Q1Finish,
        "Q2", Q2Finish,
        "Q3", Q3Finish,
        "Q4", Q4Finish
    )

3. Use the LOOKUPVALUE() function to create a new column “Quarter Identifier” in the “DCMA Summary” table.

Quarter Identifier = 
VAR Q1Finish = LOOKUPVALUE('Quarters'[Quarter Finish Date], 'Quarters'[Quarter], "Q1", 'Quarters'[Project], 'DCMA Summary'[Project])
VAR Q2Finish = LOOKUPVALUE('Quarters'[Quarter Finish Date], 'Quarters'[Quarter], "Q2", 'Quarters'[Project], 'DCMA Summary'[Project])
VAR Q3Finish = LOOKUPVALUE('Quarters'[Quarter Finish Date], 'Quarters'[Quarter], "Q3", 'Quarters'[Project], 'DCMA Summary'[Project])
VAR Q4Finish = LOOKUPVALUE('Quarters'[Quarter Finish Date], 'Quarters'[Quarter], "Q4", 'Quarters'[Project], 'DCMA Summary'[Project])
RETURN
    SWITCH(
        TRUE(),
        'DCMA Summary'[Finish] <= Q1Finish, "Q1",
        'DCMA Summary'[Finish] <= Q2Finish, "Q2",
        'DCMA Summary'[Finish] <= Q3Finish, "Q3",
        'DCMA Summary'[Finish] <= Q4Finish, "Q4",
        "Unknown"
    )

Issue 3: Stand alone Formula conversion into a table

The calculated column “Quarter Identifier” has been created in the above step and you can use this field to create quarter slicers.

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

[lbendlin]

Is there any way to move or create my stand alone meaure as a table

only temporarily - table variables can be part of the measure calculation. But the final result of the measure must be a scalar value.

Consider using EVALUATEANDLOG to examine the intermediate steps and find where you go off track.