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,

 

Can you provide some sample data to test?

 

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.