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 are related as shown below:

 

I want to create a table containing the following columns (parenthesis is the data origin table)

 

Workcenter (Tasks)

Production order (Production order)

Due date (Production order)

Time of first QC (Quality Control)

Time of second QC (Quality Control)

 

My issue is that when I try to get the times for QC I get the absolute max for every workcenter and production order.

 

Is it possible to create a workaround using DAX or is it necessary to re-create the tables as a query or view directly in SQL?

 

Thanks!

  • 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.

9 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You could create a measure like

    First QC =
SELECTCOLUMNS (
    INDEX (
        1,
        'Quality Control',
        ORDERBY ( 'Quality Control'[Checked datetime], ASC ),
        PARTITIONBY ( 'Quality Control'[Production Order No] )
    ),
    "@val", 'Quality Control'[Checked datetime]
)

    and for the second time just change the first argument to INDEX to 2 instead of 1. Make sure that your table visual is using the production order number column from the production order table and it should work.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi johnt75 

      Thanks for your response!
      I'm unable to use the INDEX function. I think it's due to the DirectQuery connection.
      I have the same issue sometimes with the CALCULATE function.

       



      • johnt75's avatar
        johnt75
        Super 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.

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 

    Is this what you're looking for?

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.