How to calculate Min or Max based on another table column value

[Bala16]

I have a scenario like this. There are two tables TableA and TableB and linked with M:M relationships.

TableA has columns ID and Value

ID Value
a 10
a 20
a 5
b 10
b 20
b 3

TableB has many columns, but only two columns are used in the matrix chart

ID product
a X1
a X2
b X1
b X2

The requirement for the matrix chart is to show MIN(TableA[Value]) if TableB[Product] = X1, if its value = X2, then show Max(TableA[Value]).

The Matrix chart should look like this

ID Product calculated value
a X1 Min value
X2 Max value
b X1 Min value
X2 Max value

I could create measures to calculate Min and Max. But, I couldn't find the way to check X1 or X2 to show corresponding calculated value.

Please anyone can help me

[amitchandak · Accepted solution]

@Bala16 , new column in Table B

= If ([Product] = "X1", minx(filter(TableA, TableA[ID] = TableB[ID]), TableA[Value]) ,  maxx(filter(TableA, TableA[ID] = TableB[ID]), TableA[Value]) )

or

= If ([Product] = "X1", min( TableA[Value]) ,  max( TableA[Value]) )

[MasonMA · Accepted solution]

Hi, 

I'd suggest reshaping your TableA a bit in Power Query to make the relationship between TableA and TableB one to many. With your sample data: 

[dinesh_7780 · Accepted solution]

Hi @Bala16 ,

Try below measure.

Calculated Value =

VAR CurrentProduct =

    SELECTEDVALUE ( TableB[Product] )

RETURN

SWITCH (

    TRUE(),

    CurrentProduct = "X1",

        CALCULATE ( MIN ( TableA[Value] ), TREATAS ( VALUES ( TableB[ID] ), TableA[ID] ) ),

    CurrentProduct = "X2",

        CALCULATE ( MAX ( TableA[Value] ), TREATAS ( VALUES ( TableB[ID] ), TableA[

ID] ) )

)

If my response as resolved your issue please mark it as solution and give kudos.

[MasonMA · Accepted solution]

Hi, 

I'd suggest reshaping your TableA a bit in Power Query to make the relationship between TableA and TableB one to many. With your sample data: 

[amitchandak · Accepted solution]

@Bala16 , new column in Table B

= If ([Product] = "X1", minx(filter(TableA, TableA[ID] = TableB[ID]), TableA[Value]) ,  maxx(filter(TableA, TableA[ID] = TableB[ID]), TableA[Value]) )

or

= If ([Product] = "X1", min( TableA[Value]) ,  max( TableA[Value]) )