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.
Hi everyone. I've looked around forums and tried a few ways but I can't seem to get a custom column for this.
Thank you to all for your time and all help appreciated.
Also, I'm trying to not use a measure so I can see the intermediate values in the calculations.
Hence my ask re a new column.
The table I'm working with has a number of lines per Work item id.
Each row of each Work item id has a unique date and state that I want to evaluate.
I just want to be able to put the earliest/min date for each state of each work item id.
The sample data below on the left is what the pertinent parts of the table are.
The picture on the right is with the additional calculated column.
When I use this formula I get the minimum date across all the rows, rather than by each Work Item Id.
ALL and ALLEXCEPT needed?
Best wishes to all.
Solved! Go to Solution.
Try
MinDatePerStatePerWorkItemId = minx(filter('PPM for BI','PPM for BI'[Work Item Id] = earlier('PPM for BI'[Work Item Id])
&& 'PPM for BI'[State] = earlier('PPM for BI'[State])),'PPM for BI'[Date])
You have to create it as new column
MinDatePerStatePerWorkItemId = minx(filter('PPM for BI','PPM for BI'[Work Item Id] = earlier('PPM for BI'[Work Item Id])
&& 'PPM for BI','PPM for BI'[State] = earlier('PPM for BI','PPM for BI'[State])),'PPM for BI'[Date])
Hi and thank you for replying @Anonymous and @amitchandak.
Amitchandak, I tried using the formula to create the column but encountered an error with the formula. See screen shot below. It appears I can't upload my pbix file to this forum which would make it easier for anyone trying to help. I've tried for about 30 mins and looked up how the earlier function works to no avail and played with the brackets and added another filter function but that didn't work either. If you would be so kind as to take a last look at what tweak is needed my day would be awesome.
Thank you.
David
Try
MinDatePerStatePerWorkItemId = minx(filter('PPM for BI','PPM for BI'[Work Item Id] = earlier('PPM for BI'[Work Item Id])
&& 'PPM for BI'[State] = earlier('PPM for BI'[State])),'PPM for BI'[Date])
Yaah! Thank you @amitchandak! That works perfectly. Thanks also @Anonymous for chiming in too.
I will read up more on this now that I have correct syntax.
All the best,
David
Hello!!
Are you looking for something like this?
MinState = CALCULATE(MIN(Hoja2[date]);
FILTER(ALL(Hoja2);
Hoja2[state] = EARLIER(Hoja2[state]) &&
Hoja2[item] = EARLIER(Hoja2[item])
)
)
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |