cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jkaelin Member
Member

Re: DAX Measure Help! (Multiple TopN criteria?)

@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!

Jkaelin Member
Member

Re: DAX Measure Help! (Multiple TopN criteria?)

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

 

Super User
Super User

Re: DAX Measure Help! (Multiple TopN criteria?)

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Jkaelin Member
Member

Re: DAX Measure Help! (Multiple TopN criteria?)

@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.  

Highlighted
Jkaelin Member
Member

Re: DAX Measure Help! (Multiple TopN criteria?)

@Greg_Deckler@TomMartens

 

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

 

Super User
Super User

Re: DAX Measure Help! (Multiple TopN criteria?)

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!