Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Getting the maximum value for each order

Hi all,   Quite often I run into an issue regarding my relationships between tables.   In this case I have information on production orders, processes and quality in three different tables. They ...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi all,

     

    I managed to solve it through af combination of the suggestions posted in the thread.

    Thanks to everyone for inputs!

     

    The solution for the time of controls was:

    FirstControl =
    MINX (
        TOPN (
            1, 
            'QualityControl', 
            'QualityControl'[CheckedDateTime], 
            DESC 
        ), 
        'QualityControl'[CheckedDateTime] 
    )

SecondControl = 
    MINX (
        TOPN (
            2, 
            'QualityControl', 
            'QualityControl'[CheckedDateTime], 
            DESC 
        ), 
        'QualityControl'[CheckedDateTime] 
    )

     

    As I actually needed the five latest times of control I just increased the TOPN value for each measure.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

Are you running the December 2022 version of Power BI Desktop? If not, try updating to the latest version. The INDEX function was only introduced in November or December I think.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Sorry for my late response.
It took some time to get Power BI Desktop updated as I'm not allowed to do it myself.

 

I've tried to use your suggestion but get the error:

"INDEX Relation paramter may have duplicate rows. That is not allowed."

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    For the first QC date you could simply use MIN('Quality Control'[Checked datetime]), for the 2nd you can try

    Second QC =
VAR Top2 =
    TOPN ( 2, 'Quality Control', 'Quality Control'[Checked datetime], ASC )
RETURN
    MINX ( Top2, 'Quality Control'[Checked datetime] )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I still have issues.

      Unfortunately I'm not able to share the data as a DirectQuery connection, but I've made some sample data in Excel. Both Excel and PBIX files are attached.

      I've tried to create both of the measures you've suggested.

       

      https://drive.google.com/drive/folders/1vQGG9PGobKcCIm8IxlvhXgwwM7RPyT1z?usp=sharing

       

      Once again, thank you very much for your help.

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        It needs to be MAXX, not MINX

        SecondControl = 
VAR Top2 =
    TOPN ( 2, 'QualityControl', 'QualityControl'[Checkeddatetime], ASC )
RETURN
    MAXX ( Top2, 'QualityControl'[Checkeddatetime] )