Forum Discussion

Jkaelin's avatar
Jkaelin
Resolver I
9 years ago
Solved

DAX Measure Help! (Multiple TopN criteria?)

Good morning,   This measure is critical to our model, but I'm afraid it's too complex for me.     Goal:  I want the "Average" of the Return Column, for the Top 2 of Factors {1,2, & 3} within the...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    Here is a full solution, you could consolidate this to a single table/measure:

     

     

    Table 1 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 1])

Table 2 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 2])

Table 3 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 3])

Table 4 = UNION('Table 1', 'Table 2', 'Table 3')

Measure = AVERAGE('Table 4'[Return])

     

TomMartens's avatar
TomMartens
Super User
9 years ago

Hey,

 

the basic idea is to union 3 tables,

as far as i understod, you already have found the proper value for factor 1.

maybe you have to use 3 filter statements, that each return a table, even if this table just contains one column.

 

Maybe you find a way to share some data 

TomMartens's avatar
TomMartens
Super User
9 years ago

Hmm, 

 

wondering what has to happen when

  • one PrimaryID satisfies more than one rule, meaning the same record is found in in the top 2 for factor 1 and factor 2
  • the 3 value is identical to the 2nd or even the Top 3 values are identical
  • Jkaelin's avatar
    Jkaelin
    Resolver I
    9 years ago

    TomMartens  If a PrimaryID satisfies more than one rule, the total records will be reduced.  So instead of 6 total records, their would be 5 (b/c 1 record met 2 criteria).  Ultimately, this measure is to backtest some stock portfolios of 30 stocks.  So the portfolio would have maybe 25-30 holdings depending on stocks that have overlapping criteria met.  Thank you for your help!