Forum Discussion

CaptainCrewe's avatar
CaptainCrewe
Frequent Visitor
9 years ago
Solved

Generate a table using SUMMARIZE and GROUPBY

  Hello   As a relative newbie to DAX, I often find myself going round and round getting tantalisingly close to a solution but missing the knowledge to make things work.  To date, head banging has...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi CaptainCrewe,

     

    You can try to use below formula to get the filter result table.

     

    Filtted Table = CALCULATETABLE(Table1,FILTER(ALL(Table1),CONTAINS(Tablel2,Tablel2[IssuerTicker],Table1[IssuerTicker],Tablel2[Max_Sum_NV_USD],Table1[Sum_NV_USD])))

     

     

    Notice: 'table1'(summarizecolumns), 'table2'(group by).

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

HI CaptainCrewe,

 

I modified your formula without test on real table, perhaps you can try it if it works on your side.

 

TestTable = 
VAR Table1 = SUMMARIZECOLUMNS(
				IssuerLookup[IssuerTicker],
				IssuerLookup[Analyst2],
				AssetLookup[Region Of Risk],
				AssetLookup[Country Of Risk],
				"Sum_NV_USD", CALCULATE(SUM(Fact_Assets[NominalAmountUSD]))
			)
VAR Table2 = SUMMARIZE(
				Table1,
				[IssuerTicker],
				"MaxSum", MAXX(FILTER(Table1,[IssuerTicker]=EARLISER([IssuerTicker])), [Sum_NV_USD])
			)
RETURN
	FILTER(
		Table1, 
		CONTAINS(Table2, [IssuerTicker], [IssuerTicker], [MaxSum], [Sum_NV_USD])
	)

 

Regards,

Xiaoxin Sheng

CaptainCrewe's avatar
CaptainCrewe
Frequent Visitor
9 years ago

It does indeed work.  I'll have to study the differences between my attempt and your solution.  On the face of it, looks like GROUPBY isn't useful in this context - though your translation of its objective via a SUMMARIZE is very useful.

 

I also note how you reference the columns in the table variable.  All extremely helpful.

 

Many thanks for helping me over this hurdle and for impressing me afresh with the power of DAX.