DAX LOOKUP

[AGo]

Hi everyone, I have this table and I have to create a table view in Power BI Desktop that displays one single row for each customer, with the max of the last event date (I've yet created a measure for this), and the type of event at this last date by customer, if there are two equal last date elements it has to return the type with the higher type code. How can I create a measure that returns this "last event type with max type code by customer"? I thought about the TOPN function with 1 row, but it returns multiple columns, and supposing to be able to exctract the single cell that I need, I don't know if in my table it will calculate it for every customer or it will return the top value in general.

Thanks in advance

[v-yulgu-msft]

Hi @AGo,

In my test, I firstly created a table view that displays records with the max of the last event date for each customer.

Table =
CALCULATETABLE (
    'last event type',
    FILTER (
        'last event type',
        'last event type'[Last Event Date]
            = CALCULATE (
                MAX ( 'last event type'[Last Event Date] ),
                ALLEXCEPT ( 'last event type', 'last event type'[Cust Code] )
            )
    )
)

Then, add a calculated column in this new table.

Rankcolumn =
RANKX (
    ALLEXCEPT ( 'Table', 'Table'[Cust Code], 'Table'[Last Event Date] ),
    [TypeCodeMeasure],
    ,
    DESC,
    DENSE
)

TypeCodeMeasure =
CALCULATE (
    SUM ( 'Table'[Type Code] ),
    ALLEXCEPT (
        'Table',
        'Table'[Cust Code],
        'Table'[Last Event Date],
        'Table'[Type]
    )
)

Create the final table view using TOPN function to return the type with the higher type code.

Top1 = TOPN(1,'Table',[Rankcolumn],ASC)

If you still have any question, please feel free to ask.

Best regards,
Yuliana Gu

[AGo · Accepted solution]

Thank you for your reply, I studied it to improve my DAX knowledge, but I found a quicker way to have a table that has what I want with a single formula .

Table formula:

Result Table = ADDCOLUMNS(ADDCOLUMNS(SUMMARIZE(Records,Records[Cust Code],"Last Event date",MAXX(FILTER(ALL(Records),Records[Cust Code]=EARLIER(Records[Cust Code])), Records[Last Event date])),"Type Code",MAXX(FILTER(ALL(Records),Records[Cust Code]=EARLIER([Cust Code])&&Records[Last Event date]=EARLIER([Last Event date])),[Type code])),"type",LOOKUPVALUE(Records[type],Records[Cust Code],[Cust Code],Records[Last Event date],[Last Event date],Records[Type code],[Type Code]))