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