cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

DAX LOOKUP

 

Cattura.JPG

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

 

1 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]))

View solution in original post

2 REPLIES 2
Microsoft
Microsoft

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] )
            )
    )
)

1.PNG

 

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]
    )
)

2.PNG

 

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

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

4.PNG

 

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

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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]))

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors