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

CaptainCrewe's avatar
CaptainCrewe
Frequent Visitor
9 years ago

That's wonderful.  Works a treat.  In my wanderings to date, I hadn't noticed or thought to try CONTAINS.

 

This will certainly get me going and I thank you for your good understanding of my problem.  As a matter of interest and to increase my DAX knowledge, are you able to confirm how my source queries and your solution might be combined?  It would be much neater to have one statement and one table rather than three.

 

I think the approach would be to use table variables, but I'm confused by the scope of these.  I tried the following and get the error "The column 'IssuerTicker' specified in the GROUPBY function was not found in the input table":

 

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 = GROUPBY(
				Table1,
				Table1[IssuerTicker],
				"MaxSum", MAXX(CURRENTGROUP(), Table1[Sum_NV_USD])
			)
RETURN
CALCULATETABLE(
	Table1,
	FILTER(
		ALL(Table1), 
		CONTAINS(Table2, Table2[IssuerTicker], Table1[IssuerTicker], Table2[MaxSum], Table1[Sum_NV_USD])
	)
)

Looking around, including your post of 08-29-2016, I suspect Table2 above can't accept Table1 as part of its definition.  Do you think there is a way to combine all these into one calculated table definition?

 

This is all a 'nice to have'.  I'm very grateful for your good assistance in solving the main problem.

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi, 

     

    I am having a similar problem but can't figure out how to modify my headusre to make SUMMARIZE work.

     

    I have a measure that isn't adding up the total correctly so i wanted to use SUMMARIZE to get around that issue but i am getting the error: "The column 'IRI/Spins Cust' specified in the 'SUMMARIZE' function was not found in the input table."

     

    Measure that doesn't add up correctly at total: 

    Actual Promo Spend = SUMX('Trade Plan 2019',[Actual Promo Units]*[Total Allowance Per Store $/Ea])
     
    Summarize measure that is giving me error: 
    Actual Promo Spend Total = SUMMARIZE('Trade Plan 2019','Customer Lookup'[IRI/Spins Cust],"Actual Promo Spend",[Actual Promo Spend])
     
    Table with data: