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

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi RGinNZ 

 

Let us try with this code first as they might solve the rest

CALCULATETABLE(
        'change-log',
        'change-log'[dataDate] = 'Get-Scenario-Details'[dataDate]
    )

The reason it is telling that is telling you that it cannot find the  name dataDate because in the context of 'change-log' table it most likely doesn't exist.  If you want to refer to a column outside of the table within FILTER or CALCULATETABLE, use EARLIER or assign it to a variable. Try either:

CALCULATETABLE(
        'change-log',
        'change-log'[dataDate] = EARLIER ( 'Get-Scenario-Details'[dataDate] )
    )

//-----------------------

VAR _dataDate = 'Get-Scenario-Details'[dataDate]
CALCULATETABLE(
        'change-log',
        'change-log'[dataDate] = dataDate )
    )

 

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