Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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.
You can try to use RANKX and EARLIER functions in DAX. Example:
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 |
New/Resale = IF(
RANKX(
FILTER(Data;EARLIER(Data[Creator User ID])=Data[Creator User ID]);
Data[Date and Time];;
ASC;
Dense
) = 1;
"New";
"Resale"
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |