Forum Discussion
Max by ID from another table
I've tried a few solutipons to similar posts and can't find one that works. I have a table with multiple records per ID, some of which have a number in them (calculated field). My other table has one...
Have you tried using Power Query for this? If you load your table 1 below and use the Group by it should give you the output you are looking for:
mark, my number column is calculated, so unfortunately it doesn't show up in power query. I guess an alternative would be to calculate the column in power query, but I'm not sure of the syntax. Would you happen to know how to get the same results in PQ?
In this formula, "Audit" is Table1 from my previous example, Incident is my ID column and PriorityNum and AuditDate are other columns not included in my simplified example.
Number =
VAR _mindatetime0or1 =
CALCULATE (
MIN ( Audit[AuditDate] ),
ALLEXCEPT ( 'Audit', 'Audit'[Incident] ),
FILTER ( 'Audit', 'Audit'[Priority Num] = 1 || 'Audit'[Priority Num] = 2 )
)
RETURN
DATEDIFF (
CALCULATE (
MIN ( Audit[AuditDate] ),
ALLEXCEPT ( 'Audit', 'Audit'[Incident] )
),
_mindatetime0or1,
MINUTE
)
/ 60