Main article in order based on order value from different table

[TedLindkvist]

Hi,

Tried to search my way but didnt really find a good match yet.

I have 2 tables,
One consisting of order with article codes and sales value
The other with article code and article names

Since I'm working with direct query, a new column isnt the most optimal, so a measure is preferred.

My task is to show the main article (i.e. the one with most sales value) for each order.

So for the example below, I would like the line of Order Number 1, to give me the value "Car" for the enire order value (33,000), since that is the largest ordervalue in that specific order number.

Tables looks something like;
Table 1

Article Code	Article name
111	Interior
112	Tyre
113	Car

Table 2 

Order Number	Article Code	Article value
1	111	1,000
1	112	2,000
1	113	30,000
2	112	2,000
2	211	20,000
2	212	5,000

[Kedar_Pande · Accepted solution]

@TedLindkvist 

Create a relationship between the two tables on the Article Code column.

Create a measure to find the main article for each order.

Main Article = 
VAR MaxArticleValue = 
CALCULATE(
MAX('Table 2'[Article value]),
ALLEXCEPT('Table 2', 'Table 2'[Order Number])
)
VAR MainArticleCode = 
CALCULATE(
MAX('Table 2'[Article Code]),
'Table 2'[Article value] = MaxArticleValue,
ALLEXCEPT('Table 2', 'Table 2'[Order Number])
)
RETURN
CALCULATE(
MAX('Table 1'[Article name]),
'Table 1'[Article Code] = MainArticleCode
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[TedLindkvist]

Worked like a charm, cheers!

[123abc]

First, create a measure that calculates the maximum article value for each order:

Below measure calculates the maximum article value for each Order Number by ignoring any filters except those on Order Number.

MaxOrderValuePerOrder = CALCULATE( MAX('Table 2'[Article value]), ALLEXCEPT('Table 2', 'Table 2'[Order Number]) )

Now, create a measure to retrieve the article name based on the highest order value:

MainArticle = CALCULATE( FIRSTNONBLANK('Table 1'[Article name], 1), FILTER( 'Table 2', 'Table 2'[Article value] = [MaxOrderValuePerOrder] && 'Table 2'[Order Number] = SELECTEDVALUE('Table 2'[Order Number]) ), 'Table 1'[Article Code] = 'Table 2'[Article Code] )

• FILTER filters Table 2 to find rows where Article value matches the maximum value for that order.
• FIRSTNONBLANK retrieves the first Article name for the matching rows.
• The relationship between Table 1 and Table 2 on Article Code allows the Article name to be pulled correctly.