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,

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