Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CRN
Helper I
Helper I

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:

CRN_0-1673352362056.png

 

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.

CRN_1-1673352952805.png

 

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!

1 ACCEPTED SOLUTION
CRN
Helper I
Helper I

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.

View solution in original post

9 REPLIES 9
CRN
Helper I
Helper I

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.

tamerj1
Super User
Super User

Hi @CRN 

Is this what you're looking for?

1.png

johnt75
Super User
Super User

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.

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.

CRN_0-1673354557126.png

 



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.

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

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

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.

It needs to be MAXX, not MINX

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors