Help with LOOKUP of MAX value

[gazpage]

HI all,

I am having some trouble with grouping / binning type analysis. My data has the following columns of interest.

Property ID: = Text string unique for each property
Asset ID: = Whole number unique to each asset. Multiple assets belong to each property
Value:= The value of each asset
Type:= A text string descriptor of a unit type

I need to filter by viz by the Type of Property (not the Type of unit, which is given in the column Type). The type for a property is the type of it's highest value asset. So for each row I need to filter the table for all the assets that have the same property ID, find the max value, and look up the associated type.

I started with a simple LOOKUPVALUE, filtered to show all the matching Property IDs, but that failed because there are ties in the max values. After much futzing around I've come up with this monstrosity.

CALCULATE (
    CALCULATE ( 
        VALUES ( Table[Type] ),
        FILTER (
            FILTER ( 
                ALLEXCEPT (Table, Table[Property ID]),
                Table[Asset ID] = MAX ( Table[Asset ID] )
            ),
            Table[Value] = MAX ( Table[Value] )     
        ),
    ALLEXCEPT (Table, Table[Property ID])
    )
)

I've used the Asset ID as a tie breaker as these are unique. However, while this works in Power BI, I need to use in Excel Power Pivot 2013, and it doesn't seem to like the MAX applied to a string.

Any help with 1. how to get past this specific issue, and 2. the proper way to do this calculation, I'd be very grateful.

 

[Ozeroth]

Hi gazpage

Your logic looks perfect, it's just a question of how to best represent it in DAX.

Here is how I would write it. Ties are broken arbitrarily (lexicographically) by Type (using FIRSTNONBLANK), and the functions used are be compatible with Excel 2013.

You can't avoid nested CALCULATE functions since you need to reference the max value for a given property.

=
CALCULATE (
    CALCULATE ( FIRSTNONBLANK ( Table[Type], 0 ), LASTNONBLANK ( Table[Value], 0 ) ),
    ALLEXCEPT ( Table, Table[Property ID] )
)

LASTNONBLANK is a convenient way of creating a filter context consisting of the max value in a column.

 

[gazpage]

Awesome, I didn't think of FIRSTNONBLANK, I don't think I've ever used it in a formula.

On the LASTNONBLANK, do I understand that there is some automatic ordering of the column? My max values are no necessarily the latest row for a given property.

 

[gazpage]

The formula provided works perfectly. However, the requirement has now changed on me.

Instead of finding the asset with the highest value for each property, I now need to find the asset Type with the highest value. Basically I need to sum up the assets by Type and then apply the logic above. I will be playing with this myself but if someone can point me in the right direction then it would be great.

I feel like maybe I should SUMMARIZE my table down and then apply the formula above the summarized table. I don't really know how to do that but will be trying it.

 

[Ozeroth]

You can do it like this:

=
CALCULATE (
    FIRSTNONBLANK (
        TOPN ( 1, VALUES ( Table[Type] ), CALCULATE ( SUM ( Table[Value] ) ) ),
        0
    ),
    ALLEXCEPT ( Table, Table[Property ID] )
)

The FIRSTNONBLANK part of the expression is similar to the TopProduct measure on this page:
Alternative use of FIRSTNONBLANK and LASTNONBLANK - SQLBI

On your question about LASTNONBLANK, the only ordering that is applied is the native ordering of the column in the first argument. That is, numbers/dates ordered numerically and strings ordered lexicographically.
So

LASTNONBLANK ( Table[Value], 0 )

is equivalent to

TOPN ( 1, VALUES ( Table[Value] ), Table[Value] )

I just like FIRSTNONBLANK and LASTNONBLANK in these situations since the expression is a bit shorter.

 

[gazpage]

Works perfectly.