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,
I have top level issues that can have one or more items underneath it. I am trying to report the completion date for each Issue that represents the completion of the latest item for that Issue.
The first table is an example of the table with Issues and Items (+ Completion Date), and I need help getting the Completion Date in the second table (without the Item).
Issue | Item | Completion Date |
511 | 1 | 2017-01-01 |
512 | 1 | |
513 | 1 | 2017-01-01 |
513 | 2 | 2017-01-31 |
514 | 1 | 2017-01-01 |
514 | 2 |
Issue | Completion Date |
511 | 2017-01-01 |
512 | |
513 | 2017-01-31 |
514 |
"Latest" gives me the date of the last completed item, but it doesn't quite work if I have issues that haven't been completed yet.
If I have multiple items and one of them has not been completed yet, I need the date value for that issue to remain blank.
Thanks.
Solved! Go to Solution.
A Power Query solution:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue", Int64.Type}, {"Item", Int64.Type}, {"Completion Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Issue"}, {{"Completion Date", each Table.Max(_,"Item")[Completion Date], Value.Type(#"Changed Type")}}) in #"Grouped Rows"
@Zubair_Muhammad thank you for your help. I tried it but got an error while trying to create the Table "A table of multiple values was supplied where a single value was expected"....
I tied another approach where I copied the date column and replaced the balnk dates with 2999-12-31. That allows me to get those using "latest".
Hugues.
A Power Query solution:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue", Int64.Type}, {"Item", Int64.Type}, {"Completion Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Issue"}, {{"Completion Date", each Table.Max(_,"Item")[Completion Date], Value.Type(#"Changed Type")}}) in #"Grouped Rows"
Hi @huguest
Please try this
Go to Modelling TAB>>>> NEW TABLE .......Then Enter this formula
New Table = SUMMARIZE ( TableName, TableName[Issue], "Completion Date", VAR mymax = CALCULATE ( MAX ( TableName[Item] ), ALLEXCEPT ( TableName, TableName[Issue] ) ) RETURN CALCULATE ( VALUES ( TableName[Completion Date] ), FILTER ( VALUES ( TableName ), TableName[Item] = mymax ) ) )
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |