Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jd8766
Helper II
Helper II

Get latest value from FACT table?

Hi, I have a customer dimension structured like the below

jd8766_0-1690915629164.png


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

jd8766_1-1690915749083.png



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

jd8766_2-1690915890412.png

How could I build this calculated column using DAX?
Thanks

1 ACCEPTED SOLUTION
ThxAlot
Super User
Super User

LatestProductName =
MAXX(
    TOPN(
        1,
        FILTER( RELATEDTABLE( 'FACT' ), 'FACT'[OrderComplete] = 0 ),
        'FACT'[OrderDate]
    ),
    'FACT'[ProductName]
)
LatestProductDate =
CALCULATE( LASTDATE( 'FACT'[OrderDate] ), 'FACT'[OrderComplete] = 0 )

ThxAlot_0-1690920724566.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

2 REPLIES 2
ThxAlot
Super User
Super User

LatestProductName =
MAXX(
    TOPN(
        1,
        FILTER( RELATEDTABLE( 'FACT' ), 'FACT'[OrderComplete] = 0 ),
        'FACT'[OrderDate]
    ),
    'FACT'[ProductName]
)
LatestProductDate =
CALCULATE( LASTDATE( 'FACT'[OrderDate] ), 'FACT'[OrderComplete] = 0 )

ThxAlot_0-1690920724566.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



DOLEARY85
Super User
Super User

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 👍
 
 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors