Multi-Column Aggregations (MC Aggregations) 04-07-2018 15:53 PM - last edited 06-18-2018 13:44 PM

[Greg_Deckler]

"I like big aggregations and I cannot lie

You other PBI'ers can't deny"

The attached PBIX file contains a slew of aggregations that span multiple columns All standard quick aggregations are included, namely:

• Max
• Min
• Sum
• Average
• Count
• Count (Distinct)
• Variance
• Standard Deviation
• Median

This techinque can be employed with as many columns as required although I use 4 in the examples. Below is the "MC Max" measure for reference. The rest are a variation on this theme:

MC Max = 
VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Value1])
VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Value2])
VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Value3])
VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Value4])
VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4)
VAR tmpValue = MAXX(tmpTable,[Column])
RETURN tmpValue

eyJrIjoiMTYyNTBhMWEtMzliZC00NGZjLWFkNjctN2YwYTIwMDMwMjYzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[sensei]

Points for creativity!

[beingandbrian]

This is beautiful

[shivvy]

Can this max formula be used with dates?

I have dates in multiple columns, some are blank or missing, but need the max date per row across multiple columns. 

[Greg_Deckler]

Yep, shouldn't be any problem.