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

Jai-Rathinavel's avatar
Jai-Rathinavel
Super 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

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

    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's avatar
      RGinNZ
      Frequent Visitor
      1 year ago

      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.
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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