Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am using DAX to calculate 4 different intervals between specific date fields, using DATEDIFF to count the days. (The date fields are: Created Date, Activated Date, Resolved Date, Closed Date.)
The four intervals are called "cycle time", "lead time", "resolved time" and "review time" and are shown in a table together with the ID of the related items.
So far it works fine.
Now I would like to do the following: instead of showing all intervals in the table I would like to select the type in a slicer and show only the selected one in the table.
So I created a table "Interval type"that contains one column "I-type" with the names of the intervals and a slicer to select them.
Then I created the following dax code:
Solved! Go to Solution.
I'm not sure what you are trying to do, but you probably need SELECTEDVALUE( ) around all those. Note you do not have a row context in a measure and DATEDIFF needs specific values as arguments. A column reference without row context won't give you one value
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
.
The error is due to row context missing and you can use "maxx"/"minx" of each date column and get it. Say
DATEDIFF (
Maxx('Work Item'[Work Item.Activated Date]),
Maxx('Work Item'[Work Item.Closed Date]),
DAY),
If it was me, I will redo differently.
a) Best way is to look at the problem again. You need these calculation data all along the project.
b) Add columns for each row as calculation of date diff ... "cycle time", "lead time", "review time", "resolved time"
c) For this report specific, use those columns in the final measure. Note: this has nothing to do with row context. You still need row context for the measure calc.
... Thanks
I'm not sure what you're trying to do, but you probably need SELECTEDVALUE( ) around all of them. Note that you do not have a row context in a measure, and DATEDIFF needs specific values as arguments. A column reference with our row context will not give you a value
Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.
Contact me privately for assistance with any large-scale BI needs, tutoring, etc.
Bless you
.
I'm not sure what you are trying to do, but you probably need SELECTEDVALUE( ) around all those. Note you do not have a row context in a measure and DATEDIFF needs specific values as arguments. A column reference without row context won't give you one value
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
.
No actual improvement there. The DAX engine is smart enough not to compute variables unless/until it really needs them.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
In addition to @AlB response, I will enhance it...
Reason: You dont need to calculate all values, and then decide the switch. You only need one calculation and can do directly in the switch.
SelectedInterval =
VAR showselected =
SELECTEDVALUE ( 'Interval type'[I-type], "no single selection" )
RETURN
SWITCH (
showselected,
"cycle time", DATEDIFF (
'Work Item'[Work Item.Activated Date],
'Work Item'[Work Item.Closed Date],
DAY),
"lead time", DATEDIFF (
'Work Item'[Work Item.Created Date],
'Work Item'[Work Item.Closed Date],
DAY),
"review time", DATEDIFF (
'Work Item'[Work Item.Resolved Date],
'Work Item'[Work Item.Closed Date],
DAY),
"resolved time", DATEDIFF (
'Work Item'[Work Item.Activated Date],
'Work Item'[Work Item.Resolved Date],
DAY),
"no single selection", 0
)
Hi,
Thanks for the quick response. Unfortunately I get an error message now, but maybe we are getting closer to the bottom of the problem:
Maybe my whole construct cannot work at all?
Best Regards
Amanita
You've messed it up in an interesting way with so many RETURNS. Try this:
SelectedInterval =
VAR showselected =
SELECTEDVALUE ( 'Interval type'[I-type], "no single selection" )
VAR CT =
DATEDIFF (
'Work Item'[Work Item.Activated Date],
'Work Item'[Work Item.Closed Date],
DAY
)
VAR ResT =
DATEDIFF (
'Work Item'[Work Item.Activated Date],
'Work Item'[Work Item.Resolved Date],
DAY
)
VAR RevT =
DATEDIFF (
'Work Item'[Work Item.Resolved Date],
'Work Item'[Work Item.Closed Date],
DAY
)
VAR LT =
DATEDIFF (
'Work Item'[Work Item.Created Date],
'Work Item'[Work Item.Closed Date],
DAY
)
RETURN
SWITCH (
showselected,
"cycle time", CT,
"lead time", LT,
"review time", RevT,
"resolved time", ResT,
"no single selection", 0
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers