Need help with a measure: iterating through times

[Luukvv93]

Hi all,

I have one fact table (first image) and a dimension time including duration per shift (dag=day and avond=evening).

For each line in the fact table (first image) I want a calculated column that calculates whether the shift is day or evening (based on the shifts in the second image).

All help is greatly appreciated

 

[AlB]

@Luukvv93

Try this for your calculated column:

WhatShift =
CALCULATE (
    DimDate[Shift],
    FILTER (
        DimDate,
        DimDate[Datum] = FactTable[DatumTijd].[Date]
            && FactTable[DatumTijd] >= DimDate[StartTijd]
            && (
                FactTable[DatumTijd] <= DimDate[EindTijd]
                    || DimDate[EindTijd] = BLANK ()
            )
    )
)

I see that there's a blank for one of the [EindTijd] values. The code assumes that's equivalent to the end of the day.   

[Luukvv93]

Hi @AlB,

After calculate it's impossible to put DimDate[Shift], any idea how to resolve? Proper connections have been made in the data model so that's not hte issue

[AlB · Accepted solution]

@Luukvv93

Sorry, it's missing the VALUES( )

WhatShift =
CALCULATE (
    VALUES(DimDate[Shift]),
    FILTER (
        DimDate,
        DimDate[Datum] = FactTable[DatumTijd].[Date]
            && FactTable[DatumTijd] >= DimDate[StartTijd]
            && (
                FactTable[DatumTijd] <= DimDate[EindTijd]
                    || DimDate[EindTijd] = BLANK ()
            )
    )
)

[Luukvv93]

@AlB Will figure it out tomorrow, thanks for the help

[AlB]

Hi @Luukvv93

You mean you want to take the Date/Time value in your first table in column 'DatumTijd' and check where that value falls in the second table, i.e.  between what StartTijd and EindTijd and from there determine whether it is Avond or Dag?

[Luukvv93]

@AlB, That's correct!