Forum Discussion

Hash2023's avatar
Hash2023
Frequent Visitor
2 years ago

Help on DAX ( FIRSTNONBLANK)

Hello Team,   I need help on a DAX statement:   Extra_Purchase_1 = CALCULATE(FIRSTNONBLANK(TransactionJournal[Extra_purchase],1),FILTER(ALL(TransactionJournal),'Id'[hotelregno_id]= TransactionJo...
General Comment
Need Help
Show and Tell
Tips and Tricks
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

Hash2023 , Check

 

New column in ID table
= Maxx(FILTER(TransactionJournal,'Id'[hotelregno_id]= TransactionJournal[hotelregno_id]), TransactionJournal[Extra_purchase])

 

 


New measure with TransactionJournal lookup
Extra_Purchase_1 = CALCULATE(FIRSTNONBLANK(TransactionJournal[Extra_purchase],1),FILTER(ALL(TransactionJournal),'TransactionJournal'[hotelregno_id]= max(TransactionJournal[hotelregno_id])))

  • Hash2023's avatar
    Hash2023
    Frequent Visitor
    2 years ago

    Hi Amitchandak,

     

    Thank you for your help. It is working but not for all record.

     

    Actually I have a table as below:

     

    RegnoExtra_dateExtra_typeExtra_purchase
    123430/09/2023Extra RevenueABCD
    123401/10/2023Not RevenueCBFR
    123429/09/2023Extra RevenueCDAB
    123402/10/2023Extra RevenueFRVJ
    202330/09/2023Extra RevenueABCD

     

    It am trying to create a new column where it only returns the Extra_purchase where the
    Extra_Type = Extra Revenue
    Extra_date = is the first date

    for each Regno.
    Is it possible?

    Many Thanks for your help.

    Kind Regards,