Forum Discussion

ny326n's avatar
ny326n
Frequent Visitor
9 years ago
Solved

How to make an array formula in DAX?

Hello!

 

Earlier I've created a formula in Excel which I now want to translate into DAX, but can't manage to figure out how. 

 

I have a table with all sales and these are some of colums:

Assignment ID

Assignment creation date and time

Creator user ID

 

What I want to create is a new column saying if the sales row is new or resale. So the fist time a Creator user ID appears it should say new and the next time it should say resale. In excel this was solved by this formula: 

{=IF(MIN(IF(E2=$E$2:$E$816;$B$2:$B$816))=B2;"New";"Resale")}

E being Creator user ID 

B being Assignment creation date and time

 

How can this be done in Power BI desktop?

 

Hope this explenation makes sense! Many thanks!

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi ny326n,

     

    Based on my test, the formula below should also work in your scenario. :smileyhappy:

    Column =
IF (
    Data[Date and Time]
        = CALCULATE (
            MIN ( Data[Date and Time] ),
            FILTER (
                ALL ( Data ),
                Data[Creator User ID] = EARLIER ( Data[Creator User ID] )
            )
        ),
    "New",
    "Resale"
)

     

    Regards

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    You can try to use RANKX and EARLIER functions in DAX. Example:

     

    1. Let's assume you have the following data (table named Data):

      Creator User IDDate and TimeAssignment ID
      12017-01-01 12:001
      22017-01-01 12:002
      12017-02-01 12:003
      22017-02-01 12:004
    2. Add new Calculated Column:

      New/Resale = IF(
       RANKX(
       FILTER(Data;EARLIER(Data[Creator User ID])=Data[Creator User ID]);
       Data[Date and Time];;
       ASC;
       Dense
       ) = 1;
       "New";
       "Resale"
      )


  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi ny326n,

     

    Based on my test, the formula below should also work in your scenario. :smileyhappy:

    Column =
IF (
    Data[Date and Time]
        = CALCULATE (
            MIN ( Data[Date and Time] ),
            FILTER (
                ALL ( Data ),
                Data[Creator User ID] = EARLIER ( Data[Creator User ID] )
            )
        ),
    "New",
    "Resale"
)

     

    Regards

    • Rahul8993's avatar
      Rahul8993
      New Member
      7 years ago

      HI, v-ljerr-msft

       

      Awesome solution, I had the same issue finally seen your post and got resolved. :-) 

       

       

    • Rahul8993's avatar
      Rahul8993
      New Member
      7 years ago

      HI, v-ljerr-msft

       

      Awesome solution, I had the same issue finally seen your post and got resolved. :-)