Find Max Value from related

[Anonymous]

I have 2 tables. They have a many to one join on companyname

Table 1 = Data (Companyname, location, State)

Table 2 = Users (lastlogin, totallogins)

How can I return the number Users.TotalLogins?

I want to presume that Table2 might have more than one totallogins for whatever reason and return the bigger number.

Thanks

[Anonymous · Accepted solution]

I think this works but is this the best method?

Largest Login = CALCULATE ( MAX ( 'Table2'[totalogins] ) )

However when I add the visual to a new card the title says Average of Largest Login

[mahoneypat · Accepted solution]

The formula you've written works in a calculated column to get the max logins.  When you use it in a visual it is automatically aggregating that column (in this case an average).  You can use the pull down to change the type of aggregation (e.g., sum), but you may want to consider just using a measure instead of a calculated column.  In that case, you might just need a measure like

Max Logins = Max(Table2[TotalLogins])

If you use that in a visual with UserID, it will automatically give you the max for that user.

If this works for you, please mark it as the solution.  Kudos are great too.  Please let me know if it doesn't or if any questions.
Regards,
Pat

[Greg_Deckler · Accepted solution]

Maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814

[Anonymous · Accepted solution]

I think this works but is this the best method?

Largest Login = CALCULATE ( MAX ( 'Table2'[totalogins] ) )

However when I add the visual to a new card the title says Average of Largest Login

[mahoneypat · Accepted solution]

The formula you've written works in a calculated column to get the max logins.  When you use it in a visual it is automatically aggregating that column (in this case an average).  You can use the pull down to change the type of aggregation (e.g., sum), but you may want to consider just using a measure instead of a calculated column.  In that case, you might just need a measure like

Max Logins = Max(Table2[TotalLogins])

If you use that in a visual with UserID, it will automatically give you the max for that user.

If this works for you, please mark it as the solution.  Kudos are great too.  Please let me know if it doesn't or if any questions.
Regards,
Pat