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 ...
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

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?

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

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

    • 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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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