How to make an array formula in DAX?

[ny326n]

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 · Accepted solution]

Hi @ny326n,

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

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]

HI, @v-ljerr-msft

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

[Rahul8993]

HI, @v-ljerr-msft

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

[Anonymous]

You can also consider using M (Power Query). See this solution: https://community.powerbi.com/t5/Desktop/Custom-column-Index-or-Ranking-by-other-column/td-p/33864.

[Anonymous]

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 ID	Date and Time	Assignment ID
   1	2017-01-01 12:00	1
   2	2017-01-01 12:00	2
   1	2017-02-01 12:00	3
   2	2017-02-01 12:00	4

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"
   )