Calculate table column based on SELECTEDVALUE from other table

[Amanita]

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: 

SelectedInterval =

var showselected = SELECTEDVALUE('Interval type'[I-type],"no single selection")

RETURN

var CT = DATEDIFF('Work Item'[Work Item.Activated Date],'Work Item'[Work Item.Closed Date],DAY)

RETURN

var ResT = DATEDIFF('Work Item'[Work Item.Activated Date],'Work Item'[Work Item.Resolved Date],DAY)

RETURN

Var RevT = DATEDIFF('Work Item'[Work Item.Resolved Date],'Work Item'[Work Item.Closed Date],DAY)

RETURN

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)

But this DAX Measure always shows the value "0" as if nothing is selected, even if I display the selected value in the table and it definitely shows the correct value:

What am I doing wrong, can you help me?
Best Regards

[sevenhills]

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

[AlB]

@Amanita

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

.

[AlB · Accepted solution]

@Amanita 

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 

.

[AlB]

@sevenhills 

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 

[sevenhills]

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
    )

[Amanita]

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

[AlB]

@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