Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a transaction table (that is connected with Business Central) which has the following relevant columns among others: Posting Date col, the name of the associates, Category, and a column with number of hours worked. I have been trying to write a measure through which I want sum the number of worked hours if date is after December 1, 2021 and the category columns are filtered based on certain conditions. Here is what I came up with:
Solved! Go to Solution.
Thansk for your feedback @AlexisOlson.
I tried your code and even though it worked, it gave me a different result that I could not really verify. However, when I realized that the filtering of the Poste_Date column in my transaction table is no different than the other filterings then I wrote the following and got the right result:
CALCULATE(
SUM(
'Ärendetransaktioner_Excel'[Quantity]
),
'Ärendetransaktioner_Excel'[Entry_Type] ="usage",
'Ärendetransaktioner_Excel' [Type] = "Resource",
'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE(),
'Ärendetransaktioner_Excel'[Posting_Date] >= DATE(2021,12,01)
)
🙂
The basic problem appears to be mismatching parentheses.
Instead of this
MAX ( Excel[Posting_Date] >= DATE ( 2021, 12, 01 ) )
I think you want this:
MAX ( Excel[Posting_Date] ) >= DATE ( 2021, 12, 01 )
assuming [Posting_Date] is a table column and not a measure.
Another issue is that IF needs to return the same data type for both branches, not a numeric quantity in some cases, and text "-" for others.
Try this:
Total Worked Hours =
IF (
MAX ( 'Ärendetransaktioner_Excel'[Posting_Date] ) >= DATE ( 2021, 12, 01 ),
CALCULATE (
SUM ( 'Ärendetransaktioner_Excel'[Quantity] ),
KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Entry_Type] = "usage" ),
KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Type] = "Resource" ),
KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Chargeable_PGS] = TRUE () )
),
0
)
Thansk for your feedback @AlexisOlson.
I tried your code and even though it worked, it gave me a different result that I could not really verify. However, when I realized that the filtering of the Poste_Date column in my transaction table is no different than the other filterings then I wrote the following and got the right result:
CALCULATE(
SUM(
'Ärendetransaktioner_Excel'[Quantity]
),
'Ärendetransaktioner_Excel'[Entry_Type] ="usage",
'Ärendetransaktioner_Excel' [Type] = "Resource",
'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE(),
'Ärendetransaktioner_Excel'[Posting_Date] >= DATE(2021,12,01)
)
🙂
@Anonymous , Try like
Total Worked Hours =
IF(
MAX('Ärendetransaktioner_Excel'[Posting_Date]) >= DATE(2021,12,01) ,
CALCULATE(
SUM(
'Ärendetransaktioner_Excel'[Quantity]
),
FILTER( 'Ärendetransaktioner_Excel', 'Ärendetransaktioner_Excel'[Entry_Type] ="usage" && 'Ärendetransaktioner_Excel' [Type] = "Resource" && 'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE() )
)
)
Thanks for your quick response. && makes the code cleaner, but is not solving the problem with the first argument of my IF statement. Still get error message!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |