Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two tables.
I would like to pull latest Milestone and Status from 'Reviews' into the 'allChart' table by program.
In a table with a matching single program # I would like to return the Latest Milestone with an E in the title so the result would be
E3 Exit
Any ideas would be great.
This is what I have tried thus far,
It returned all blanks
Excel Data
PBI File
Hi, @Brett007
Not clear what you want to do, is the target table in the PBIX file what you want to get, how do you get the max, min and target? This case doesn't look very difficult to implement, but I can't get a clear idea of what you want to do, just like shooting arrows in the dark.
Could you please consdier sharing more details about it for further discussion?
Best Regards,
Community Support Team _ Zeon Zheng
Hey @Brett007 ,
my assumptions are both tables are not related and both tables are called first table and second table, then try this DAX statement to create a calculated column in second table:
status from 1st table =
var __program = 'first table'[Program]
var LatestMilestone =
MAXX(
FILTER ( 'first table'
, 'first table'[Program] = __programm && LEFT( 'first table'[Program] , 1 ) = "E"
)
, 'first'[Milestone]
)
Hopefully, this returns what you are looking for.
If not create a pbix file that contains sample data, but still reflects your data model (tables, data types of the columns, relationships, calculated columns, and relevant measures) Upload the file to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well.
Regards,
Tom
I was not able to get your formula to work. I tried to modify it. I posted the results in the original question above. Thank you for your assistance.
Hi,
What other possible entries can you have in the Milestone column? What do you mean by "Latest Milestone with an E in the title"?
I updated my post with example data
Hi,
Try these calculated column formulas
Latest date with an E = CALCULATE(MAX(Reviews[Date]),FILTER(Reviews,Reviews[Program Number]=EARLIER(allChart[Program Number])&&LEFT(Reviews[Review],1)="E"))
Latest review = LOOKUPVALUE(Reviews[Review],Reviews[Program Number],allChart[Program Number],Reviews[Date],[Latest date with an E])
Latest status = LOOKUPVALUE(Reviews[Status],Reviews[Program Number],allChart[Program Number],Reviews[Date],[Latest date with an E])
Hope this helps.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |