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  Even though you can create a calculated column at DAX query level you cannot save the created column back to the model. Only measures can be defined and written back to the model as of now. If you are looking for just having a calculated column at DAX query view. You can try copy pasting the below DAX. Also note that while defining variables (Var RelatedChangedLog) they should be intialized before the EVALUATE statement. Please check my below DAX and see if that helps.

 

DEFINE
VAR RelatedChangeLog = 
    CALCULATETABLE(
        'change-log',
        'change-log'[dataDate] = MAX('Get-Scenario-Details'[dataDate]) //Since you mentioned only one row is present in Get-Scenario Table
    )
EVALUATE
    SUMMARIZE(
        RelatedChangeLog,"GroupbyColumnName",'change-log'[yourGroupbyColumnName],
        "TotalSales",
        SUM('change-log'[metrics.LogicChanges]) +
        SUM('change-log'[metrics.ActivitiesAdded]) +
        SUM('change-log'[metrics.FlaggedChanges]) +
        SUM('change-log'[metrics.AllCalendarChanges]) +
        SUM('change-log'[metrics.CalendarChanges]) +
        SUM('change-log'[metrics.ActivitiesDeleted]) +
        SUM('change-log'[metrics.ActivityChanges]) +
        SUM('change-log'[metrics.NearCriticalChanges]) +
        SUM('change-log'[metrics.WorkingDayChanges]) +
        SUM('change-log'[metrics.DurationChanges]) +
        SUM('change-log'[metrics.DelayedActivityChanges]) +
        SUM('change-log'[metrics.CriticalChanges])
    )

 

Replace the grouby column name with the relevant name.

 

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