cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Need help with creating a key index

Hello,

So basically I have a FactSheet containing production data and I need to calculate the total shift time per production line.

Each productionline follows a D (Day) shift and a A (Night) shift. Since the staff occupation is highly flexible shift times are different for each production line.

Following the sample data below on 9 oct 2018 for production line 22 consider the day shift to start at 11:29 and end on 13:30.

Hence I used to create a KeyIndex:

```DateShiftLijnIndex =
CONCATENATE(FactProductie[ProductieDatum];FactProductie[Shift])&FactProductie[Lijn]```

For now all is fine, however when the night shift works over midnight the production date will have 2 values (the startday and the next day). In order to solve this I have to calculate the min date for each DateShiftLijnIndex.

Below I created the measure that I believe will solve the issue, but I need somebody to complete it

```MinDate =
VAR MinDate = MIN(FactProductie[ProductieDatum])
RETURN
CALCULATE(MinDate;
FILTER(VALUES(FactProductie[DateShiftLijnIndex]```

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Need help with creating a key index

Hi again,

1. You are missing the filter function around the table argument in the MINX function

2. You should not compair the date column to the variable, but the Index column to the variable.

This is how it should be:

VAR index = IndexColumn

RETURN

MINX(
FILTER(
Table,
IndexColumn = index
);
DateColumn
)

Regards,

Kristjan

4 REPLIES 4
Member

## Re: Need help with creating a key index

I am not sure if I understand this well enough, but why not use the start date in your key? But on the other hand you can get the min start date for your key with

```MinDate =
VAR key = FactProductie[DateShiftLijnIndex]
RETURN
MINX(
FILTER(
FactProductie;
FactProductie[DateShiftLijnIndex] = key
);
FactProductie[ProductieDatum]  // or the startdate
)```

Regards,

Kristjan

Frequent Visitor

## Re: Need help with creating a key index

Hi @Kristjan76,

Thanks for rapid answer. Agree with your logic, only issue left is that DAX is unable to compare values of date with string. The error considers the use of VALUE or FORMAT. Any clue on how to apply this to the formula?

Member

## Re: Need help with creating a key index

Hi again,

1. You are missing the filter function around the table argument in the MINX function

2. You should not compair the date column to the variable, but the Index column to the variable.

This is how it should be:

VAR index = IndexColumn

RETURN

MINX(
FILTER(
Table,
IndexColumn = index
);
DateColumn
)

Regards,

Kristjan

Frequent Visitor

## Re: Need help with creating a key index

Fantastic it works thanks so much