Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
snph1777
Helper V
Helper V

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

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:

 

 

 

jan 21.png

 

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.

3 ACCEPTED SOLUTIONS
Fowmy
Super User
Super User

@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

 

Fowmy_0-1705870104712.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Daniel29195
Super User
Super User

Hello @snph1777 

this is the output : 

Daniel29195_0-1705870084273.png

 

Daniel29195_1-1705870092322.png

 

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,

 

 

 

View solution in original post

@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 👍

View solution in original post

11 REPLIES 11
snph1777
Helper V
Helper V

@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
Helper V
Helper V

I learned an important DAX function called REMOVFILTERS today!

Daniel29195
Super User
Super User

Hello @snph1777 

this is the output : 

Daniel29195_0-1705870084273.png

 

Daniel29195_1-1705870092322.png

 

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,

 

 

 

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

Fowmy
Super User
Super User

@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

 

Fowmy_0-1705870104712.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@ 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?

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

 




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@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 👍

OK I will

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

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.