Forum Discussion

jd8766's avatar
jd8766
Helper II
3 years ago
Solved

Get latest value from FACT table?

Hi, I have a customer dimension structured like the below


I have a FACT table like the below (joined using CustomerKey)



I want to add a column to my customer dimension table which gets me the latest Product Name, and Order Date from the FACT table where OrderComplete = 0. It should get me the one that has the latest Order date...So this would give me an output of the below

How could I build this calculated column using DAX?
Thanks

Need Help
Show and Tell
  • ThxAlot's avatar
    ThxAlot
    3 years ago
    LatestProductName =
MAXX(
    TOPN(
        1,
        FILTER( RELATEDTABLE( 'FACT' ), 'FACT'[OrderComplete] = 0 ),
        'FACT'[OrderDate]
    ),
    'FACT'[ProductName]
)
    LatestProductDate =
CALCULATE( LASTDATE( 'FACT'[OrderDate] ), 'FACT'[OrderComplete] = 0 )

2 Replies

  • ThxAlot's avatar
    ThxAlot
    Super User
    3 years ago
    LatestProductName =
MAXX(
    TOPN(
        1,
        FILTER( RELATEDTABLE( 'FACT' ), 'FACT'[OrderComplete] = 0 ),
        'FACT'[OrderDate]
    ),
    'FACT'[ProductName]
)
    LatestProductDate =
CALCULATE( LASTDATE( 'FACT'[OrderDate] ), 'FACT'[OrderComplete] = 0 )

  • DOLEARY85's avatar
    DOLEARY85
    Resident Rockstar
    3 years ago

    Hi,

     

    try creating a these measures:

     

    Measure= CALCULATE(VALUES(Transactions[Product Name]),USERELATIONSHIP(Customer[Customer Key],Transactions[Customer Key]),LASTDATE(Transactions[Order Date]))
     
    Measure 2 = CALCULATE(VALUES(Transactions[Order Date]),USERELATIONSHIP(Customer[Customer Key],Transactions[Customer Key]),LASTDATE(Transactions[Order Date]))
     
    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍