Aggregating columns in different ways for the same instance

[BekahLoSurdo]

I have a financial dataset with multiple values (total contract value, revenue, etc.) per project rolled up by month. Sample data:

Project	Contract Value	Budget Revenue	Date.Month
ABC	20000	0	1/1/2017
ABC	20000		2/1/2017
ABC	30000	0	3/1/2017
ABC	40000	60000	4/1/2017
ABC	50000	0	5/1/2017
DEF	10000		1/1/2017
DEF	20000		2/1/2017
DEF	50000	20000	3/1/2017
DEF	30000	30000	4/1/2017
DEF	20000	40000	5/1/2017
GHI	70000		1/1/2019
GHI	70000	0	2/1/2019
GHI	80000	60000	3/1/2019
GHI	70000	20000	4/1/2019
GHI	90000	0	5/1/2019

I am attempting to summarize it in a data model with an eye on performance (the actual dataset is much larger with more values per project). The issue is that I have different qualifications for which values should prevail per project i.e.:

- The most recent month's Contract Value per project

- The most recent non-zero, non-blank Budget Revenue per project

Expected result:

Project	Contract Value	Budget Revenue
ABC	50000	60000
DEF	20000	40000
GHI	90000	20000

Since the qualifications are independent from one another (ultimate values could be from different rows), I'm having trouble doing this with less than four tables - and that's for this simplified example. Being new to DAX, I am fairly certain that I'm missing a simplification step here in the way of a nested query or something. 

Thank you in advance!

Tables:

Data (original dataset)

FilteredBudRev =
CALCULATETABLE ( Data,
FILTER ( Data, Data[Budget Revenue] <> 0 )
)

BudRevResult =
SELECTCOLUMNS (
FILTER ( FilteredBudRev, FilteredBudRev[Date.Month]=FilteredBudRev[MaxBudRevDate] ),
"Project", FilteredBudRev[Project],
"Budget Revenue", FilteredBudRev[Budget Revenue]
)

ContractResult =
SELECTCOLUMNS (
FILTER ( Data, Data[Date.Month]=Data[MaxOverallDate] ),
"Project", Data[Project],
"Contract Value", Data[Contract Value]
)

Basically, I created a new table based on the original dataset that only contains the valid values for Budget Revenue. Then I created columns in each of these tables to show the max date per project (within the unique properties of each table). Then I created two more tables (one for Contract Value and one for Budget Revenue) that contained the values of these measures on their respective max dates. Finally, I created a relationship between these two tables to concatenate these columns together to get my expected result. Too convoluted, right?

Calculated Columns:

MaxOverallDate =
CALCULATE (
MAX ( Data[Date.Month] ),
ALLEXCEPT ( Data, Data[Project] )
)

MaxBudRevDate =
CALCULATE (
MAX ( FilteredBudRev[Date.Month] ),
ALLEXCEPT ( FilteredBudRev, FilteredBudRev[Project] )
)

 

Budget Revenue = RELATED ( BudRevResult[Budget Revenue])

[v-juanli-msft]

Hi @BekahLoSurdo 

Is this problem sloved?

If not, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @BekahLoSurdo 

If i understand correctly from your information, you worked so complicatedly to solve your problem, you want a simple way instead.

Per your requirements, to get better performance for a large data, instead of create calculated table, 

1. if you want to filter data without 0 values/null, you could filter "Budget Revenue" column in Edit queries,

in this way, it doesn't need to create a new table.

2.If you need to change the data model to get your "Expected result" , go to Edit queries,

Create M code as below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLDoAgDESvYliTQCmKLv3rGQz3v4YQIRaqJKSEN22nvS4xTrOQwuhwQowXFCijwQkva9zEN8dIspFjm3CXouWSllRoKV7WLVoi/YHjX3sPzsWzDLkkT4BfFssmeZrC5n6c4ctxm8MH1q9NivtqTfhfwZQ2qWTgmwzY3w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, #"Contract Value" = _t, #"Budget Revenue" = _t, Date.Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Contract Value", Int64.Type}, {"Budget Revenue", Int64.Type}, {"Date.Month", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom1", each let project=[Project] in List.Max(Table.SelectRows(#"Changed Type", each [Project] =project)[Date.Month])),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Custom1", "Custom.1", each if [Date.Month] = [Custom1] then 1 else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column1", each [Custom.1] <> null and [Custom.1] <> ""),
    table1 = Table.RemoveColumns(#"Filtered Rows1",{"Budget Revenue", "Date.Month", "Custom1", "Custom.1"}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each [Budget Revenue] <> null and [Budget Revenue] <> ""),
    #"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each [Budget Revenue] <> 0),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows3", "Custom2", each let project=[Project] in List.Max(Table.SelectRows(#"Filtered Rows3", each [Project] =project)[Date.Month])),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Custom2", "Custom.2", each if [Date.Month] = [Custom2] then 1 else null),
    #"Filtered Rows4" = Table.SelectRows(#"Added Conditional Column2", each [Custom.2] <> null and [Custom.2] <> ""),
    table2 = Table.RemoveColumns(#"Filtered Rows4",{"Contract Value", "Date.Month", "Custom2", "Custom.2"}),
    #"Merged Queries" = Table.NestedJoin(table1,{"Project"},table2,{"Project"},"table2",JoinKind.LeftOuter),
    #"Expanded query" = Table.ExpandTableColumn(#"Merged Queries", "table2", {"Budget Revenue"}, {"table2.Budget Revenue"})
in
    #"Expanded query"

Please download my pbix to see more details.

You could click on the "applied pane" to see each step.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BekahLoSurdo]

Thank you, @v-juanli-msft .

I really like how this is all done in one query, it makes for a much more streamlined process. The steps (filter the data as required per each column, strip out the necessary values per project per column, merge them all back together) are essentially the same but this way is much more succinct, which is what I was looking for. 

Thank you for your help!

[v-juanli-msft]

Hi @BekahLoSurdo 

It seems the columns"Project", "Contract Value","Budget Revenue" and "Date.Month" are from different tables.

Which table do they come from?

Could you show example data of these tables and relationships among them?

Best Regards

Maggie

[BekahLoSurdo]

I was having trouble attaching images yesterday but it seems to be working now, my apologies for the delay. This should help clarify: