How to find the maximum value of a measure in table

[Maggi029]

I need to find the maximum value of a measure.

Here I have attached replica of my original data with dummy values and column table.

Relationship: Table1[Group] and Table2[Group] are connect using Many to many relationship.

Query:

Here I need to find the maximum value of a measure called  CombinedDensity in table 2 ,

Here the formula I have used  MaxCombinedDensity = MaXX(Table 2, CombinedDensity)

But it is showing wrong maximum value

Table 1:

Date	Group	Category	Table1value	Table1Density Measure(Table1value /100)
10/10/2021	Group1	Category1	100	1
10/10/2021	Group2	Category1	300	3
10/10/2021	Group3	Category2	400	4
10/10/2021	Group4	Category3	450	4.5

Table 2:

Date	Group	Table2value	Table2Density Measure(Table2value /10)	CombinedDensity Measure(Table2Density / Table1Density)
10/10/2021	Group1	600	60	60
10/10/2021	Group2	200	20	6.66
10/10/2021	Group3	100	10	2.5
10/10/2021	Group4	350	35	7.77

@MFelix  can you please look into this and it s also related to this query Solved: How to plot maximum/minimum value plot for selecte... - Microsoft Power BI Community

[v-binbinyu-msft · Accepted solution]

Hi @Maggi029 ,

My test table and model:

Table1:

Table2:

Model:

Create three measures and add it to visuals, then get the maximum value for CombinedDensity measure in Card visual

Table1Density =
MAX ( Table1[Table1value] ) / 100

Table2Density =
MAX ( Table2[Table2value] ) / 10

CombinedDensity =
DIVIDE ( Table2[Table2Density], Table1[Table1Density] )

MaxCombinedDensity =
MAXX ( Table2, [CombinedDensity] )

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Maggi029 ,

Based on the example you shared the formula is correct:

However believe that the problem is that you are trying to make the calculation based on a many to many relationship, bu only picking up a single table on your calculation so this will not get the correct result because the final value is lacking information.

Can you share a little bit more information on the error? What is the result you are getting?

[arichard19]

Row and Filter Level context is most likely causing your issue (https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/)

If the data was mine, I would choose one of the two options:
1. Simply merge the two tables together in power query
2. Use a LOOKUP() function to pull the related data into row level in Table 2 (similar to VLOOKUP() in excel)

For either method you will need to create a unique key that relates Table 1 with Table 2 within both tables. I believe this would be a combination of Date & Group - for example, 10/10/2021Group1

Using this, you can either merge or look up Table1value into Table2

From there, you will want to calculate using Calculated Columns instead of measures before measuring the MAX of CombinedDensity

Column[Table2Density] =  Table2Value / 10
Column[LookupKey] = Date & Group
Column[Table1Value] = Lookup(Table1[Table1Value], Table1[LookupKey], Table2[LookupKey])
Column[CombinedDensity] = Column[Table2Density] / Column[Table1Value] 

Measure[MaxCombinedDensity] = MAXX(Table2, Column[CombinedDensity] )

[Maggi029]

For some reason LookUpValue function returns below mentioned error 

A table of multiple values was supplied where a single value was expected.