Multi-Column Aggregations (MC Aggregations)

"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 a...

MultiColumnAggregations.pbix32 KB

Mathematical

Totals

gsksarepta

Helper II

3 years ago

Hello, Thank you Greg_Deckler EtnaEstrella dmalque for this multicolumn aggregations. Can I know how to get the column name of where this aggregation came from ? I am using this to finding a limiting material that is low in supply. I found the minimum value with the formula and I need to get the name of that column. Can anyone help ? Thank you.

Greg_Deckler

Community Champion

3 years ago

gsksarepta Well, you could check for the existance of that value in the temp tables like:

MC Min = 
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 = MINX(tmpTable,[Column])
VAR __Result = 
  SWITCH(TRUE(),
    tmpValue IN __tmpCol1, "Value1",
    tmpValue IN __tmpCol2, "Value2",
    tmpValue IN __tmpCol3, "Value3",
    "Value4"
  )
RETURN __Result