Forum Discussion

RGinNZ's avatar
RGinNZ
Frequent Visitor
1 year ago
Solved

Beginner needs help debugging Dax Query

Hi, I need help with a Power BI Dax Query please.

I have never had any Power BI training and don't really know what I am doing...

 

Background

In Power BI Desktop, I have have set a relationship between two tables:

 

The 'Get-Scenario-Details'.[dataDate] column has only one row.

 

The 'change-log'.[dataDate] column has many rows.

 

I am trying to return the SUMX calculated value (which "seems to working in the query since there are no errors).

The calculation is a sumx of many columns in the 'Change-log' table on the row where the dataDate matches the dataDate in the 'Get-Scenario-Details'.[dataDate] column.

 

Desired Result:

I want the returned SUMX value to be inserted as a new colum named TotalChanges in the 'Get-Scenario-Details' table.

The 'Get-Scenario-Details' table does not currently have a column named TotalChanges, I want the Dax Query to create it.

 

Here is the Dax Query that is not working:

 

 

EVALUATE
TotalChanges = 
VAR RelatedChangeLog = 
    CALCULATETABLE(
        'change-log',
        'change-log'[dataDate] = 'Get-Scenario-Details'[dataDate]
    )
RETURN
    SUMX(
        RelatedChangeLog,
        'change-log'[metrics.LogicChanges] +
        'change-log'[metrics.ActivitiesAdded] +
        'change-log'[metrics.FlaggedChanges] +
        'change-log'[metrics.AllCalendarChanges] +
        'change-log'[metrics.CalendarChanges] +
        'change-log'[metrics.ActivitiesDeleted] +
        'change-log'[metrics.ActivityChanges] +
        'change-log'[metrics.NearCriticalChanges] +
        'change-log'[metrics.WorkingDayChanges] +
        'change-log'[metrics.DurationChanges] +
        'change-log'[metrics.DelayedActivityChanges] +
        'change-log'[metrics.CriticalChanges]
    )

 

 

 

Errors

There are 2 errors in the code highlighted by Power BI:

 

Error #1 - re: line 2 the error says: Failed to resolve name 'TotalChanges'. It is not a valid table, variable, or function name.

I tried to preface it with a line reading: VAR TotalChanges - that did not work

 

Error #2 - re: line 6 which reads: 'change-log'[dataDate] = 'Get-Scenario-Details'[dataDate]
in this line, it returns an error on the red text that says cannot find the name 'dataDate' 

 

Any help would be much appreciated, thank you.

Need Help
  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    1 year ago

    RGinNZ , I have added the comments. You can add your own comments by using "//" for single lines and "/* your text */" for multines like I have mentioned below.

    // Only measures works on filter context i.e when you want to filter by dynamic dates
TotalChanges = 
//Initialising a variable MaxDate to capture the selected date on the slicer
VAR MaxDate = CALCULATE(MAX('change-log-summary'[dataDate]), ALLEXCEPT('change-log-summary', 'change-log-summary'[dataDate]))
RETURN
/*Below expression sums up all the mentioned columns after filtering the selected date (MaxDate)*/
CALCULATE(
    SUMX(
        FILTER('change-log-summary', 'change-log-summary'[dataDate] = MaxDate),
        'change-log-summary'[metrics.ActivitiesAdded] +
        'change-log-summary'[metrics.ActivitiesDeleted] +
        'change-log-summary'[metrics.ActivityChanges] +
        'change-log-summary'[metrics.AllCalendarChanges] +
        'change-log-summary'[metrics.CalendarChanges] +
        'change-log-summary'[metrics.CriticalChanges] +
        'change-log-summary'[metrics.DelayedActivityChanges] +
        'change-log-summary'[metrics.DurationChanges] +
        'change-log-summary'[metrics.FlaggedChanges] +
        'change-log-summary'[metrics.LogicChanges] +
        'change-log-summary'[metrics.NearCriticalChanges] +
        'change-log-summary'[metrics.WorkingDayChanges]
    )
)

     

    Did I answer your question ? Please mark this post as solution

     

    Thanks,

    Jai

14 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi RGinNZ 

     

    Let us try with this code first as they might solve the rest

    CALCULATETABLE(
        'change-log',
        'change-log'[dataDate] = 'Get-Scenario-Details'[dataDate]
    )

    The reason it is telling that is telling you that it cannot find the  name dataDate because in the context of 'change-log' table it most likely doesn't exist.  If you want to refer to a column outside of the table within FILTER or CALCULATETABLE, use EARLIER or assign it to a variable. Try either:

    CALCULATETABLE(
        'change-log',
        'change-log'[dataDate] = EARLIER ( 'Get-Scenario-Details'[dataDate] )
    )

//-----------------------

VAR _dataDate = 'Get-Scenario-Details'[dataDate]
CALCULATETABLE(
        'change-log',
        'change-log'[dataDate] = dataDate )
    )

     

    • RGinNZ's avatar
      RGinNZ
      Frequent Visitor
      1 year ago

      Hi danextian,

       

      Thanks for the suggestions.

      I tried both but they did not work.

      Both tables already contain a dataDate column

       

      Please see my response to Jai-Rathinavel (Solution Sage), he seems to be on the right track ...

       

      Kind Regards,

      RGinNZ

      • danextian's avatar
        danextian
        Super User
        1 year ago

        Did you use the formula to create a calculatead column or use DAX query view. If you're looking to create a calculated then create on in the table itself, DAX query view can't do that.

         

        TotalChanges Calc Column = 
// This calculated column sums various change metrics from the 'change-log' table based on the dataDate.

VAR RelatedChangeLog = 
    CALCULATETABLE(
        'change-log', 
        // Filters the 'change-log' table to rows where the dataDate matches the dataDate in 'Get-Scenario-Details'
        'change-log'[dataDate] = EARLIER('Get-Scenario-Details'[dataDate])
    )

RETURN 
    SUMX(
        RelatedChangeLog, 
        // Sums the values of the following columns in the filtered 'change-log' table:
        'change-log'[metrics.LogicChanges] + 
        'change-log'[metrics.ActivitiesAdded] + 
        'change-log'[metrics.FlaggedChanges] + 
        'change-log'[metrics.AllCalendarChanges] + 
        'change-log'[metrics.CalendarChanges] + 
        'change-log'[metrics.ActivitiesDeleted] + 
        'change-log'[metrics.ActivityChanges] + 
        'change-log'[metrics.NearCriticalChanges] + 
        'change-log'[metrics.WorkingDayChanges] + 
        'change-log'[metrics.DurationChanges] + 
        'change-log'[metrics.DelayedActivityChanges] + 
        'change-log'[metrics.CriticalChanges]
    )

         

  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    Super User
    1 year ago

    RGinNZ  Even though you can create a calculated column at DAX query level you cannot save the created column back to the model. Only measures can be defined and written back to the model as of now. If you are looking for just having a calculated column at DAX query view. You can try copy pasting the below DAX. Also note that while defining variables (Var RelatedChangedLog) they should be intialized before the EVALUATE statement. Please check my below DAX and see if that helps.

     

    DEFINE
VAR RelatedChangeLog = 
    CALCULATETABLE(
        'change-log',
        'change-log'[dataDate] = MAX('Get-Scenario-Details'[dataDate]) //Since you mentioned only one row is present in Get-Scenario Table
    )
EVALUATE
    SUMMARIZE(
        RelatedChangeLog,"GroupbyColumnName",'change-log'[yourGroupbyColumnName],
        "TotalSales",
        SUM('change-log'[metrics.LogicChanges]) +
        SUM('change-log'[metrics.ActivitiesAdded]) +
        SUM('change-log'[metrics.FlaggedChanges]) +
        SUM('change-log'[metrics.AllCalendarChanges]) +
        SUM('change-log'[metrics.CalendarChanges]) +
        SUM('change-log'[metrics.ActivitiesDeleted]) +
        SUM('change-log'[metrics.ActivityChanges]) +
        SUM('change-log'[metrics.NearCriticalChanges]) +
        SUM('change-log'[metrics.WorkingDayChanges]) +
        SUM('change-log'[metrics.DurationChanges]) +
        SUM('change-log'[metrics.DelayedActivityChanges]) +
        SUM('change-log'[metrics.CriticalChanges])
    )

     

    Replace the grouby column name with the relevant name.

     

    Thanks,

    Jai 

    • RGinNZ's avatar
      RGinNZ
      Frequent Visitor
      1 year ago

      Hi Jai,

       

      Thanks for telling me that 'you cannot save the created column back to the model. Only measures can be defined and written back to the model as of now.'

       

      would something like this work?

      • Create a quick measure that sums the values of the columns ALLEXCEPT [dataDate] in the change-log table.
      • Then add a quick measure in the Get-Scenario-Details that creates a new column and somehow insert the TotalChanges value from the change-log table.

      If yes, any pointers on how o do it would help...

       

      Kind Regards,

      RGinNZ

      • Jai-Rathinavel's avatar
        Jai-Rathinavel
        Super User
        1 year ago

        RGinNZ  Unforntunately it is not possible. Calculated column are evaluated during the model refresh. Even if you create a calculated column based on a measure it will still not update the latest values. So I would recommend creating the TotalSales as a measure in the model. So that it will be dynamic. 

        TotalSales = 
    SUMX(
        'change-log',
        'change-log'[metrics.LogicChanges] + 
        'change-log'[metrics.ActivitiesAdded] + 
        'change-log'[metrics.FlaggedChanges] + 
        'change-log'[metrics.AllCalendarChanges] + 
        'change-log'[metrics.CalendarChanges] + 
        'change-log'[metrics.ActivitiesDeleted] + 
        'change-log'[metrics.ActivityChanges] + 
        'change-log'[metrics.NearCriticalChanges] + 
        'change-log'[metrics.WorkingDayChanges] + 
        'change-log'[metrics.DurationChanges] + 
        'change-log'[metrics.DelayedActivityChanges] + 
        'change-log'[metrics.CriticalChanges]
    )
  • RGinNZ's avatar
    RGinNZ
    Frequent Visitor
    1 year ago

    Hi Jai,

     

    I've created a new measure like this in the Change-log table:

    Column = TotalChanges = SUMX( 'change-log', [metrics.LogicChanges] + [metrics.ActivitiesAdded] + [metrics.FlaggedChanges] + [metrics.AllCalendarChanges] + [metrics.CalendarChanges] + [metrics.ActivitiesDeleted] + [metrics.ActivityChanges] + [metrics.NearCriticalChanges] + [metrics.WorkingDayChanges] + [metrics.DurationChanges] + [metrics.DelayedActivityChanges] + [metrics.CriticalChanges] )

    I am getting the following error:

    Failed to resolve name 'TotalChanges'. It is not a valid table, variable, or function name.

     

    TotalChanges is intended to be the name of the new column.

     

    Can you please suggest a fix?

      • RGinNZ's avatar
        RGinNZ
        Frequent Visitor
        1 year ago

        Hi Jai,

        Here is a new measure I created to try and solve the problem.

        It is a screenshot so I could show the table also.

        It is returning an error:

        Query (5, 1) The expression specified in the query is not a valid table expression.

         

        Can you please suggest a fix?

        Here is the code for the query:

        EVALUATE
        VAR LatestDate = MAX('change-log-summary'[dataDate])
        RETURN
            SUMX(
                FILTER(
                    'change-log-summary',
                    'change-log-summary'[dataDate] = LatestDate
                ),
                [metrics.LogicChanges]
                + [metrics.ActivitiesAdded]
                + [metrics.FlaggedChanges]
                + [metrics.AllCalendarChanges]
                + [metrics.CalendarChanges]
                + [metrics.ActivitiesDeleted]
                + [metrics.ActivityChanges]
                + [metrics.NearCriticalChanges]
                + [metrics.WorkingDayChanges]
                + [metrics.DurationChanges]
                + [metrics.DelayedActivityChanges]
                + [metrics.CriticalChanges]
            )
         
        Thanks for your help.