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])

     

Jkaelin's avatar
Jkaelin
Resolver I
9 years ago

Greg_Deckler TomMartens

 

Maybe I'm mistaken, but this formula is returning a Semantic #Error, "The measure refers to Multiple Columns".  I think the [Factor 2] is causing it to error, but the logic seems to work.  What am I missing??

 

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

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

You need to create it as a table, not a measure.

  • Jkaelin's avatar
    Jkaelin
    Resolver I
    9 years ago

    Greg_Deckler

     

    Thanks for the tips.  I may just be in over my head here because I'm not sure what you mean by create it as a table vs. a measure.  I only have done calculated columns & measures.  Sorry for my ignorance.  

    • Jkaelin's avatar
      Jkaelin
      Resolver I
      9 years ago

      Greg_DecklerTomMartens

       

      I believe I got the measure to get me the correct value.  I think it works now!  Thank you guys so much for your help.  

       

      DAX Test Measure :=
      CALCULATE (
          AVERAGEX (
              UNION (
                  TOPN ( 2, Table1, Table1[Factor 1] ),
                  TOPN ( 2, Table1, Table1[Factor 2] ),
                  TOPN ( 2, Table1, Table1[Factor 3] )
              ),
              [Return]
          ),
          FILTER ( Table1, [Valuation] <= PERCENTILE.INC ( [Valuation], 0.5 ) )
      )

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        9 years ago

        Yep, that should definitely work as a single measure.

         

        For future reference you can create a calculated table by going to the Modeling tab and clicking "New Table". The difference is that a table expects to return 1 or more rows versus a measure which expects a single value to be generated.