Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

If statement with Circular Dependency

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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