max of two different tables with the same columns that are in a 1:* relationship

[jday]

I am looking for a way to take the max of a column that is in two different tables.  

The data looks something like this 

table 1

x      y    z     

1     2     2

2     2     2

3     3     3 

4     4     4

5     5     5

table 2

x     y     z

1    4     4

1    2     4

2    1     1 

3    6     6

3    4     6

3    4     6

4    1     1

5    4     4

Desired Results 

x          z     

1          4

2          2

3          6

4          4

5          5

If there are any other ways to solve this problem I am open to changing my approach.  The end goal is to have a dashboard that will take the max after slicing away results from Y.  

[v-ljerr-msft]

Hi @jday,

Based on my test, you should be able to use the formula below to create a new calculate column in table1 to get the max value of z from both table1 and table2.

maxZ =
IF (
    Table1[z] >= CALCULATE ( MAX ( Table2[z] ), RELATEDTABLE ( Table2 ) ),
    Table1[z],
    CALCULATE ( MAX ( Table2[z] ), RELATEDTABLE ( Table2 ) )
)

Regards

[jday]

Hey @v-ljerr-msft,

This is really close to what I need.  I forgot to post this, but I need a slicer to apply to these tables before they calculate a max.  With what you provided it is currently taking the max of the two z columns (which is what I need).  I need Y to be able to act as a slicer though.  

[v-ljerr-msft]

Hi @jday,

Try using the formula below to create a new measure, then show the measure with Table1[x], Table1[y] column on a Table visual. It should work in your scenario.

maxZ = 
IF (
    MAX ( Table1[z] ) >= CALCULATE ( MAX ( Table2[z] ), RELATEDTABLE ( Table2 ) ),
    MAX ( Table1[z] ),
    CALCULATE ( MAX ( Table2[z] ), RELATEDTABLE ( Table2 ) )
)

Regards

[jday]

Hey @v-ljerr-msft,

I created 2 fake datasets so I could show you what I was wanting.  Currently when I use this solution I get everyone who falls into that category.  

What I want is to select the max of x that would be applied after slicing on a variable. 

The data I created is below.  I will also show what my desired results are. 

Table 2

x	y	z
1	Green	3
2	Blue	2
3	Red	6
4	Yellow	4
5	Orange	5
6	Orange	5
7	Red	6
8	Black	1
9	Black	1
10	Yellow	4

Table 1

x	y	z
1	red	6
1	blue	2
1	red	6
1	red	6
1	blue	2
1	blue	2
1	yellow	4
2	green	3
2	green	3
2	red	6
2	blue	2
3	yellow	4
3	orange	5
3	black	1
3	red	6
3	red	6
3	blue	2
4	red	6
4	yellow	4
5	green	3
6	yellow	4
6	red	6
6	red	6
7	orange	5
8	red	6
8	red	6
8	red	6
8	red	6
8	red	6
8	red	6
8	red	6
8	yellow	4
8	orange	5
8	green	3
8	green	3
8	green	3
8	green	3
9	green	3
9	green	3
9	orange	5
9	orange	5
10	red	6

Max of every x member. 

x	red	orange	yellow	green	blue	black
1	6	0	4	3	2	1
2	6	0	0	3	2	1
3	6	0	0	0	2	1
4	6	0	4	0	0	1
5	0	5	0	3	0	1
6	6	5	4	0	0	1
7	6	5	0	0	0	1
8	6	5	4	3	0	1
9	0	5	0	3	2	1
10	6	0	4	0	0	1

These results are displaying the max as 6 for every value in x.  

The desired results would actually be something like this. 

Lets assume everyone falls into 'black' as a default if they do not have a max of something else. 

		filter
z	count distinct  of x	y = everything but red
1	0
2	1
3	1
4	3
5	5
6	0
		filter
z	count distinct  of x	y = Everything but red and orange
1	1
2	1
3	3
4	5
5	0
6	0
		filter
z	count distinct  of x	y = only blue and black
1	6
2	4
3	0
4	0
5	0
6	0

Please let me know if I can provide anything else that would be of help.  

[jday]

@v-ljerr-msft,

Your solution will work if I could filter the data before the max was taken.  Do you know if this would be possible?  

[v-ljerr-msft]

Hi @jday,

Could you try the formula below to see if it works in your scenario? 

maxZ =
VAR selectedY =
    FIRSTNONBLANK ( Table1[y], 1 )
RETURN
    IF (
        MAX ( Table1[z] )
            >= CALCULATE (
                MAX ( Table2[z] ),
                FILTER ( RELATEDTABLE ( Table2 ), Table2[y] = selectedY )
            ),
        MAX ( Table1[z] ),
        CALCULATE (
            MAX ( Table2[z] ),
            FILTER ( RELATEDTABLE ( Table2 ), Table2[y] = selectedY )
        )
    )

Regards