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
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.
Solved! Go to 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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hello @snph1777
this is the output :
can you please give this dax code a try :
*---------------
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,
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.
if this answer has successfully addressed your issue, kindly consider marking it as an accepted solution! Dont forget to hit thumbs up buttton 👍
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.
I learned an important DAX function called REMOVFILTERS today!
Hello @snph1777
this is the output :
can you please give this dax code a try :
*---------------
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
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
⭕ 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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
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
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
108 | |
108 | |
91 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
102 | |
86 |