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

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] )