If statement with Circular Dependency

[Anonymous]

Hey all,

I am having an issue that I don't quite understand (which is likely due to my lack of understanding of circular dependencies). From how I understand circular dependencies, A depends on B so B cannot depend on A. In my current situation, I created an if statement with only one column reference, yet I am getting a circular dependency error. Obviously the if statement depends on the column in the if statement, but why does Power Bi think that column depends on the if statement?

Any ideas?

[Anonymous]

What is the formula for the column 'NOT-Worked'[Days in Status (Base Work)] ?

Either way, I would create this 'NOT-Worked'[Column 2] column using query editor.  You'll save space and have better performance.

You can click on "Add Column" in the ribbon, and then "Add Conditional Column"

From the GUI, you can select that if Column [Days in Status (Base Work)] = null, then 0, else the same [Days in Status (Base Work)] column.

[Anonymous]

Hey @Anonymous

'NOT-Worked'[Days in Status (Base Work)] is a calculated column containing an if statement. Within that if statement are 2 other calculated columns, 'NOT-WORKED'[- Status Date] and 'NOT-WORKED'[Base Range]. 'NOT-WORKED'[- Status Date] is based on todays date and as far as I know TODAY() cannot be used in the query editor so 'NOT-Worked'[Days in Status (Base Work)] is not present in the editor.

Here is a small sample of the incredibly long 'NOT-Worked'[Days in Status (Base Work)]

IF (
    'NOT-WORKED'[- Status Date] <= 0 + CALCULATE (
                AVERAGE ( 'NOT-WORKED'[Base Range] ),
                ALL ( Vlookup[Status] )
            )
        && 'NOT-WORKED'[- Status Date] >= -10 + CALCULATE (
                    AVERAGE ( 'NOT-WORKED'[Base Range] ),
                    ALL ( Vlookup[Rehab Status] )
                ),
    "Upcoming",

[Anonymous]

to get the equivalent TODAY()  expression in Query Editor using M, you need to use the following code:

DateTime.Date( DateTime.LocalNow() )

DateTime.LocalNow() will give you a timestamp of the current day and time.

Wrapping that in DateTime.Date()  will give you only the date portion.

But, looking at your calculated column, you're referencing some measures that require DAX, and you won't be able to calculate those in Query Editor anyway...

I don't think we can solve this anymore without the entire formula for 'NOT-Worked'[Days in Status (Base Work)], and probably a screenshot of the data model relationship view

[Anonymous]

@Anonymous

I think I may be able to accomplish it all in the editor with the DateTime.Date( DateTime.LocalNow() ). I'm going to give it a shot. In the mean time here whole formula. Its just the same type of logic as the portion I supplied repeated over and over.

Days in Status (Base Work) =
IF (
    'NOT-WORKED'[- Status Date] <= 0 + CALCULATE (
                AVERAGE ( 'NOT-WORKED'[Base Range] ),
                ALL ( Vlookup[Status] )
            )
        && 'NOT-WORKED'[- Status Date] >= -10 + CALCULATE (
                    AVERAGE ( 'NOT-WORKED'[Base Range] ),
                    ALL ( Vlookup[Rehab Status] )
                ),
    "Upcoming",
    IF (
        'NOT-WORKED'[- Status Date] > 0 + CALCULATE (
                    AVERAGE ( 'NOT-WORKED'[Base Range] ),
                    ALL ( Vlookup[Rehab Status] )
                )
            && 'NOT-WORKED'[- Status Date] <= 15 + CALCULATE (
                        AVERAGE ( 'NOT-WORKED'[Base Range] ),
                        ALL ( Vlookup[Status] )
                    ),
        "0 to 15 days",
        IF (
            'NOT-WORKED'[- Status Date] > 15 + CALCULATE (
                        AVERAGE ( 'NOT-WORKED'[Base Range] ),
                        ALL ( Vlookup[Status] )
                    )
                && 'NOT-WORKED'[- Status Date] <= 30 + CALCULATE (
                            AVERAGE ( 'NOT-WORKED'[Base Range] ),
                            ALL ( Vlookup[Status] )
                        ),
            "16 to 30 days",
            IF (
                'NOT-WORKED'[- Status Date] > 30 + CALCULATE (
                            AVERAGE ( 'NOT-WORKED'[Base Range] ),
                            ALL ( Vlookup[Status] )
                        )
                    && 'NOT-WORKED'[- Status Date] <= 45 + CALCULATE (
                                AVERAGE ( 'NOT-WORKED'[Base Range] ),
                                ALL ( Vlookup[Status] )
                            ),
                "31 to 45 days",
                IF (
                    'NOT-WORKED'[- Status Date] > 45 + CALCULATE (
                                AVERAGE ( 'NOT-WORKED'[Base Range] ),
                                ALL ( Vlookup[Status] )
                            ),
                    "46+ days",
                    "x"
                )
            )
        )
    )
)

and the relationships

[Anonymous]

use variables...this will run MUCH faster

In this case you're only calculating the Status Average and Rehab Average ONCE, and then reusing the same value each time.  You also only need to maintain 1 copy of the code, and any changes to make to it are automatically applied to all instances of that variable.

Use SWITCH() instead of nested IF() statements, much easier to read.  SWITCH( TRUE(), ...) is even more flexible, and lots of documentation exists on this pattern.

Days in Status (Base Work) =
VAR Status_Average =
    CALCULATE ( AVERAGE ( 'NOT-WORKED'[Base Range] ), ALL ( Vlookup[Status] ) )
VAR Rehab_Average =
    CALCULATE (
        AVERAGE ( 'NOT-WORKED'[Base Range] ),
        ALL ( Vlookup[Rehab Status] )
    )
RETURN
    SWITCH (
        TRUE (),
        'NOT-WORKED'[- Status Date]
            <= 0 + Status_Average
            && 'NOT-WORKED'[- Status Date]
            >= -10 + Rehab_Average, "Upcoming",
        'NOT-WORKED'[- Status Date]
            > 0 + Status_Average
            && 'NOT-WORKED'[- Status Date]
            <= 15 + Rehab_Average, "0 to 15 days",
        'NOT-WORKED'[- Status Date]
            > 15 + Status_Average
            && 'NOT-WORKED'[- Status Date]
            <= 30 + Rehab_Average, "16 to 30 days",
        'NOT-WORKED'[- Status Date]
            > 30 + Status_Average
            && 'NOT-WORKED'[- Status Date]
            <= 45 + Rehab_Average, "31 to 45 days",
        'NOT-WORKED'[- Status Date]
            > 45 + Status_Average, "46+ days",
        "x"
    )