Beginner needs help debugging Dax Query

[RGinNZ]

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.

[RGinNZ]

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?

[Jai-Rathinavel]

@RGinNZ  just remove "Column =" from your DAX. The DAX should start from "TotalChanges ="

[RGinNZ]

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.

[Jai-Rathinavel]

@RGinNZ DAX query always expects the result in a table format so you just have to wrap your measure inside ROW function like below.

EVALUATE
VAR LatestDate = MAX('change-log-summary'[dataDate])
VAR TotalSum =     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]
    )
RETURN
ROW("TotalSumValue",TotalSum)

Answered your query ? Please mark this post as a solution.

Thanks,

Jai

[RGinNZ]

Hi Jai,

Thanks for all of your help.

I have finally found the solution, largely based on variations of solutions you suggested. This is the one that worked:

TotalChanges = VAR MaxDate = CALCULATE(MAX('change-log-summary'[dataDate]), ALLEXCEPT('change-log-summary', 'change-log-summary'[dataDate]))

RETURN

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]

    )

)

Since you assisted me a lot in finding this solution, I would like to credit you with the solution. If you can please post a response with appropriate comment lines (see below) then I will mark it as the solution.

Here are some notes of what comment line notes may be:

First, please note that it should be added as a new measure, not as a DAX query - at least, that's how it worked for me, I never got the Dax  attempts to work...

"Add Comment lines to clarify for others how the measure works".

Älso describe the end result, something like:

"This adds a new measure into the table with the name TotalChanges that sums selected colunms for the filtered row (MaxDate in this example). The returned value can then be a datasource for a visual."

Thanks again Jai, and thanks also to everyone else who assisted me.

[Jai-Rathinavel · Accepted solution]

@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

[Anonymous]

Hi @RGinNZ ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @RGinNZ ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Jai-Rathinavel]

@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]

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]

@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]
    )

[danextian]

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]

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]

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]
    )