Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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)...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous,

 

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?

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Fantastic it works thanks so much