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 telling me that 'you cannot save the created column back to the model. Only measures can be defined and written back to the model as of now.'

 

would something like this work?

  • Create a quick measure that sums the values of the columns ALLEXCEPT [dataDate] in the change-log table.
  • Then add a quick measure in the Get-Scenario-Details that creates a new column and somehow insert the TotalChanges value from the change-log table.

If yes, any pointers on how o do it would help...

 

Kind Regards,

RGinNZ

Jai-Rathinavel's avatar
Jai-Rathinavel
Icon for Super User rankSuper User
1 year ago

RGinNZ  Unforntunately it is not possible. Calculated column are evaluated during the model refresh. Even if you create a calculated column based on a measure it will still not update the latest values. So I would recommend creating the TotalSales as a measure in the model. So that it will be dynamic. 

TotalSales = 
    SUMX(
        'change-log',
        '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]
    )