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.
Dear Power BI community
I need help to write a DAX formular that can identify the first value in multiple rows with the same ID based on date/time. I have a table as shown below and need to calculate the column "First assign team" marked in red. I have just filled out the values in "First assign team" manually for easy understanding.
I am able to identify the first date/time within the rows with same ID with the following formular, but I cannot figure out how to identify the first assign team.
First assignment time = CALCULATE(MIN(Table1[Assign date/time]);FILTER(Table1;Table1[Ticket ID]=EARLIER(Table1[Ticket ID])))
If anyone could help, it would be highly appreciated.
Best regards,
Jonas
Solved! Go to Solution.
Hi @spocx,
First Assign team = VAR firstassigndate = CALCULATE ( MIN ( 'table 1'[Assign date/time] ), ALLEXCEPT ( 'table 1', 'table 1'[Ticket ID] ) ) RETURN CALCULATE ( SELECTEDVALUE ( 'table 1'[Team assigned] ), FILTER ( ALLEXCEPT ( 'table 1', 'table 1'[Ticket ID] ), 'table 1'[Assign date/time] = firstassigndate ) )
Best regards,
Yuliana Gu
Hello, I have a similar problem but in my case i need the distict count of the Id´s.
Because it has lines with the same id but diferent direction the dax calcuate count 2 times this id.
I want to make a dax that counts these tables and if it finds the repeated id counts only one.
Hi @spocx,
First Assign team = VAR firstassigndate = CALCULATE ( MIN ( 'table 1'[Assign date/time] ), ALLEXCEPT ( 'table 1', 'table 1'[Ticket ID] ) ) RETURN CALCULATE ( SELECTEDVALUE ( 'table 1'[Team assigned] ), FILTER ( ALLEXCEPT ( 'table 1', 'table 1'[Ticket ID] ), 'table 1'[Assign date/time] = firstassigndate ) )
Best regards,
Yuliana Gu
HI
I have a very similar scenario, where for a project number I have multiple rows for various periods. The status changes in some periods. However, I want to see the first status for a particular project number. Using the formula above shows always just Unapproved status for some reason. E.G. for C20220510-T I should see as the first status 'Approved', for C20220514 I should see Unapproved, for C20220529-T I should see Approved. Can you help please?
Hi Yuliana
Your solution works perfectly. Thank you so much.
Best regards,
Jonas
@spocx try this
First assignment time = CALCULATE(MIN(Table1[Assign date/time]),ALLEXCEPT(Table1, Table1[Ticket ID]))
assign team for first assignment= IF([First assignment time]=Table1[Assign date/time],Table1[Team Assigned],NULL)
First assign team = CALCULATE(MAX(assign team for first assignment),ALLEXCEPT(Table1, Table1[Ticket ID]))
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |