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 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's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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