cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luukv93 Frequent Visitor
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]
 

 

tempsnip.png

 

 

tempsnip.png

 

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
Highlighted
Kristjan76 Member
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
Kristjan76 Member
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

 

Luukv93 Frequent Visitor
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?

 

 

tempsnip.png

Highlighted
Kristjan76 Member
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

Luukv93 Frequent Visitor
Frequent Visitor

Re: Need help with creating a key index

Fantastic it works thanks so much