Find lowest value in two tables by ID

[Anonymous]

I'm having trouble getting a good results here for either a measure or a new calculated table. I have two joined tables with ID and priority fields. The first table will ahve multiple values for the same ID and the other table will have one. I need to look across both tables and then return the lowest value by ID. I don't care whether it's a new table or measure to get the result. Example below:

Table1:

ID              Priority

123	1
123	2
123	3
456	3
456	3
456	4

Table2:

ID             Priority

123	4
456	2

Desired Result:

ID              Priority

123	1
456	2

[Ashish_Mathur]

Hi,

Using the Query Editor, append data from both tables to create a new table.  Now in your visual, drag the ID and write this measure

=MIN(Data[Priority])

Hope this helps.

[Anonymous]

---

@Ashish_Mathur wrote:

Using the Query Editor, append data from both tables to create a new table.  Now in your visual, drag the ID and write this measure

=MIN(Data[Priority])

---

Might be easier with this rational to do 

Measure =
        CALCULATE ( MINX ( UNION ( Table1, Table2 ), [Priority] ) )

[Ashish_Mathur]

Hi,

A calculate function should not be required in your formula.

[Anonymous]

You're right, it's implicit.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Other solutions.

1. Add a calculated column in Table 2.

Column =
MIN ( CALCULATE ( MIN ( 'Table1'[Priority] ) ), [Prority] )

2. A measure with the 'Table 2'[ID] in a Table visual.

Measure =
MIN ( MIN ( 'Table1'[Priority] ), MIN ( 'Table2'[Prority] ) )

Best Regards,
Dale

[Anonymous]

Thanks Dale!

[LivioLanzo]

As a calculated table, you can do it like this:

Table = 
GROUPBY(
    UNION( Table1, Table2 ),
    Table1[ID], 
    "Priority", MINX( CURRENTGROUP(), [Priority] )
)