Microsoft Power BI - DAX - SUMMARIZE + ADDCOLUMNS - complicated Calculated Table using variable

[snph1777]

I have a Microsoft Power BI (.pbix) file. I need to develop a Calculated Table using DAX language.

(I do not want a Power Query based solution.)

Scenario:

I have a simple table called 'SourceInput', it has data for an EmployeeId, DeviceId, and OperationDate.

The above three are the proper GROUP BY columns, in SQL terms.

The table has one more column called OperationStartTime.

Now, for each combination of the first 3 columns (EmployeeId, DeviceId, and OperationDate), we need to derive the MINIMUM OperationStartTime, with a condition:

Get the MINIMUM OperationStartTime for a given combination of EmployeeId, DeviceId, and OperationDate, only if the OperationStartTime falls after 12:00 hrs, i.e get the MINIMUM OperationStartTime AT or AFTER 12 PM for a given combination of EmployeeId, DeviceId, and OperationDate.

If there is no OperationStartTime at or after 12 PM for a given combination of EmployeeId, DeviceId, and OperationDate, then we show BLANK().

Note that while the OperationDate column is a date, the OperationStartTime column has a datetime value.

Please see the image file below:

I have developed a simple DAX based Calculated Table as below, this works fine, you can see the .pbix file.

DesiredOuput_DAX_CalculatedTable

=

ADDCOLUMNS (

              SUMMARIZE(

                         SourceInput,

                         SourceInput[EmployeeId],

                         SourceInput[DeviceId],

                         SourceInput[OperationDate],

                         SourceInput[OperationStartTime]

                       ),

              "EarliestOperationStartTimeAfter12PM", CALCULATE(

                                                                  MIN(SourceInput[OperationStartTime]),

                                                                  FILTER(

                                                                          VALUES(SourceInput[OperationStartTime]),

                                                                          HOUR(SourceInput[OperationStartTime]) >= 12

                                                                         --i.e. after 12 PM OperationStartTime only--

                                                                        )

                                                                )

           )

However, in my real project, the 'SourceInput' table has too many columns, and we need to remove the undesired ones, so I had to use DAX variables for the intermediate steps.

I developed an equivalent DAX based Calculated Table as below, using another source table called 'SourceInputExpanded' with variables to store data for the intermediate steps:

DesiredOuput_DAX_CalculatedTable_WithVariables

=

                    VAR SourceInput_CT_Variable = SELECTCOLUMNS(

                                                                    SourceInputExpanded,

                                                                   "EmployeeId1", SourceInputExpanded[EmployeeId],
                                                                   "DeviceId1", SourceInputExpanded[DeviceId],
                                                                   "OperationDate1", SourceInputExpanded[OperationDate],
                                                                   "OperationStartTime1", SourceInputExpanded[OperationStartTime]

                                                                   --remove undesired columns using this calculated table variable--

                                                               )

                    VAR Output_CT_Variable = ADDCOLUMNS (

                                                           SUMMARIZE(

                                                                        SourceInput_CT_Variable,

                                                                        [EmployeeId1],
                                                                        [DeviceId1],
                                                                        [OperationDate1],
                                                                        [OperationStartTime1]

                                                                     ),

                                                           "EarliestOperationStartTimeAfter12PM", CALCULATE(

                                                                                                               MINX(

                                                                                                                       SourceInput_CT_Variable,

                                                                                                                       [OperationStartTime1]

                                                                                                                   ),

                                                                                                              FILTER(

                                                                                                                      SUMMARIZE(

                                                                                                                                  SourceInput_CT_Variable,

                                                                                                                                  [OperationStartTime1]

                                                                                                                               ),

                                                                                                                      HOUR([OperationStartTime1]) >= 12

                                                                                                                     --i.e. after 12 PM OperationStartTime only--

                                                                                                                    )

                                                                                                            )

                                                         )

                    RETURN Output_CT_Variable

I am not getting the desired output.

Where am I in error in my second approach with variables ?

Can someone correct this ?

I have not fully adapted to using a DAX calculated table variable, and its' columns, there are some tricky logics in the syntax related to its' lineage....

The EarliestOperationStartTimeAfter12PM column of the output DAX Calculated Table is incorrect, the GROUP BY part is not working in the second case.

One major requirement: the output DAX Calculated Table must have the same number of rows as the source input table.

There is some theory on how the filter context interacts with a DAX Calculated Table variable,
and how context transition happens, I am missing this.

[Fowmy · Accepted solution]

@snph1777 

Create the table as follows:

New Table = 
VAR __T1 = 
    SUMMARIZE(
        SourceInput ,
        SourceInput[EmployeeId],
        SourceInput[DeviceId],
        SourceInput[OperationDate],
        SourceInput[OperationStartTime]
    )
VAR __T2 = 
    ADDCOLUMNS(
        __T1,    
        "MinTime" , 
        CALCULATE( 
            MIN( SourceInput[OperationStartTime] ) , 
            REMOVEFILTERS( SourceInput[OperationStartTime] ) , 
            HOUR(SourceInput[OperationStartTime]) > 12 
        )
    )   
RETURN
    __T2

[Daniel29195 · Accepted solution]

Hello @snph1777 

this is the output : 

can you please give this dax code a try : 

DesiredOuput_DAX_CalculatedTable_WithVariables =

VAR SourceInput_CT_Variable =

    ADDCOLUMNS (

        SUMMARIZE (

            SourceInputExpanded,

            SourceInputExpanded[Employeeld],

            SourceInputExpanded[Deviceld],

            SourceInputExpanded[OperationDate],

            SourceInputExpanded[OperationStartTime] --remove undesired columns using this calculated table variable--

        ),

        "EarliestOperationStartTimeAfter12PM",

            CALCULATE (

                CALCULATE (

                    MIN ( SourceInputExpanded[OperationStartTime] ),

                    HOUR ( SourceInputExpanded[OperationStartTime] ) >= 12 --i.e. after 12 PM OperationStartTime only--

                ),

                REMOVEFILTERS ( SourceInputExpanded[OperationStartTime] )

            )

    )

RETURN

    SourceInput_CT_Variable

*---------------

If my response has successfully addressed your issue, kindly consider marking it as the accepted solution ✅. This not only helps you keep track of resolved matters but also assists other community members who may encounter similar challenges. Additionally, your acknowledgment by giving a thumbs up 👍 is greatly appreciated. Thank you for contributing to the community's collaborative spirit.

best regards,

[snph1777]

@Daniel29195

@Fowmy

Both of your solutions are correct; i needed to add REMOVEFILTERS or ALLEXCEPT on multiple columns (on top of the existing code shared already) .

Appreciate it.

[snph1777]

I learned an important DAX function called REMOVFILTERS today!

[Daniel29195 · Accepted solution]

Hello @snph1777 

this is the output : 

can you please give this dax code a try : 

DesiredOuput_DAX_CalculatedTable_WithVariables =

VAR SourceInput_CT_Variable =

    ADDCOLUMNS (

        SUMMARIZE (

            SourceInputExpanded,

            SourceInputExpanded[Employeeld],

            SourceInputExpanded[Deviceld],

            SourceInputExpanded[OperationDate],

            SourceInputExpanded[OperationStartTime] --remove undesired columns using this calculated table variable--

        ),

        "EarliestOperationStartTimeAfter12PM",

            CALCULATE (

                CALCULATE (

                    MIN ( SourceInputExpanded[OperationStartTime] ),

                    HOUR ( SourceInputExpanded[OperationStartTime] ) >= 12 --i.e. after 12 PM OperationStartTime only--

                ),

                REMOVEFILTERS ( SourceInputExpanded[OperationStartTime] )

            )

    )

RETURN

    SourceInput_CT_Variable

*---------------

If my response has successfully addressed your issue, kindly consider marking it as the accepted solution ✅. This not only helps you keep track of resolved matters but also assists other community members who may encounter similar challenges. Additionally, your acknowledgment by giving a thumbs up 👍 is greatly appreciated. Thank you for contributing to the community's collaborative spirit.

best regards,

[snph1777]

 @Daniel29195     thanks very much for your help; appreciate it. 

[Fowmy · Accepted solution]

@snph1777 

Create the table as follows:

New Table = 
VAR __T1 = 
    SUMMARIZE(
        SourceInput ,
        SourceInput[EmployeeId],
        SourceInput[DeviceId],
        SourceInput[OperationDate],
        SourceInput[OperationStartTime]
    )
VAR __T2 = 
    ADDCOLUMNS(
        __T1,    
        "MinTime" , 
        CALCULATE( 
            MIN( SourceInput[OperationStartTime] ) , 
            REMOVEFILTERS( SourceInput[OperationStartTime] ) , 
            HOUR(SourceInput[OperationStartTime]) > 12 
        )
    )   
RETURN
    __T2

[snph1777]

@ Fowmy    When there are more columns in my table, I am not getting the right output, based on your solution.

Apart from EmployeeId, Device Id, I have a fe wother coluumns including OperationEndTime. How should I use REMOVEFILTERS then?

[Fowmy]

@snph1777 

You can enhance the provided code by incorporating additional filters using the KEEPFILTERS function. This function allows you to apply more than one filter.

Alternatively, you can achieve a similar effect using ALLEXCEPT. This function removes all filters from the specified table, except the ones mentioned in the subsequent arguments

You can use ALLEXCEPT instead of KEEPFILTER 

 ALLEXCEPT( SourceInput , SourceInput[EmployeeId] , SourceInput[DeviceId] , SourceInput[OperationDate] )

[Daniel29195 · Accepted solution]

@snph1777 

instead of removefliters,

use ALLEXCEPT(table_name , employeid , deviceid ,  operationdate ) , 

you can add as many columns as you want (  the columns that you want to leave the filter from. 

https://dax.guide/allexcept/

if this answer has successfully addressed your issue, kindly consider marking it as an accepted solution! Dont forget to hit thumbs up buttton 👍

[snph1777]

OK I will

[snph1777]

@Fowmy     thanks very much for your help; appreciate it. 

[snph1777]

In my actual data model, I have OperationStartTime and OperationEndTime.

If I use REMOVEFILTERS(SourceInput[OperationStartTime], SourceInput[OperationEndTime]), i.e. on both columns ,i think it works!