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

Thank you for your response.

 

I'm not sure in what form you need the sample data.  For now, I'll show a couple of extracts from my DAX Studio queries.  The first shows the initial grouping by Ticker, Analyst, Region and Country with a Sum of the NominalAmount to be used as a tie-breaker, obtained from the SUMMARIZECOLUMNS function:

 

IssuerTickerAnalyst2Region Of RiskCountry Of RiskSum_NV_USD
AAAJim SmithAfricaMorocco600000
BBBAmy BloggsNorth AmericaCanada1656090590
CCCMary DoeSouth AmericaChile894679350
DDDJoe BrownAsiaChina22909716756
DDDJoe BrownAsiaHong Kong18898282952
DDDJoe BrownAsiaMacao1762612500
DDDJoe BrownAsiaSingapore562370067
DDDJoe BrownAustraliaAustralia8865500
DDDJoe BrownEuropeFrance1185432644
DDDJoe BrownEuropeLuxembourg11250000
DDDJoe BrownEuropeUnited Kingdom552160000
EEEMary DoeSouth AmericaChile1293134082
FFFDavid JonesNorth AmericaCanada3179842513


I turned this into a calculated table in my model (IssuerTickerToCountryOfRisk) so as to query it more easily and find the MAX(SUM_NV_USD).  

 

Using the GROUPBY function, I can find the maximum Sum_NV_USD for a given ticker:

 

IssuerTickerMax_Sum_NV_USD
AAA600000
BBB1656090590
CCC894679350
DDD22909716756
EEE1293134082
FFF3179842513

 

What I'm aiming for is a calculated table with only one row per ticker.  Where a ticker has more than one combination of Region and Country (ie DDD in this extract), then I need to select the row with the highest Sum_NV_USD.  Easy to do in SQL and I fear I may be bringing too much of that mindset to this problem.  I feel there must be a more elegant way to return the desired rows.

 

IssuerTickerAnalyst2Region Of RiskCountry Of RiskSum_NV_USD
AAAJim SmithAfricaMorocco600000
BBBAmy BloggsNorth AmericaCanada1656090590
CCCMary DoeSouth AmericaChile894679350
DDDJoe BrownAsiaChina22909716756
EEEMary DoeSouth AmericaChile1293134082
FFFDavid JonesNorth AmericaCanada3179842513

 

The final objective is to use this summarized table of ticker, analyst, region and country as a replacement lookup table for reports where a single analyst, region and/or country is wanted for a given ticker.  The Sum_NV_USD isn't needed since it can come from a measure calculation over the underlying fact table.

 

Thanks again for taking an interest

Anonymous's avatar
Anonymous
Not applicable
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.