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]= TransactionJournal[hotelregno_id]))
 
I think that the DAX statement is not currently working due to the fact that TransactionJournal[Extra_purchase] is in Text format.
 
Is there a way where i came use the TransactionJournal[Extra_Date] as a filter to take the first value?
 
Thank you.
 
Kind Regards,
General Comment
Need Help
Show and Tell
Tips and Tricks

2 Replies

  • 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,