Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

TOPN with previous month

Hello people, 

 

I try to show a table with just 50 lines, and i try to create a measure to show just the TOP50 values from the last month 

 

 

 

>> and I get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

 

P.S: I can't create calculated column because i'm connected with DirectQuery

 

Help me pls

 

J.O.

Need Help
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Anonymous,

     

    Modify your measure as below:

     

    Last_Month_price=
VAR _value=CALCULATE(MAX(fact_snapshot[last_amt]),PREVIOUSMONTH('Calendar'[Date]))
VAR _table=TOPN(50,fact_snapshot,_value,ASC)
Return
MAXX(_table,_value)

     

     

    Best Regards,
    Kelly

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

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try like

     

    all(Fact_snapshot)

    or

    allselected(Fact_snapshot)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak ,

       

      Thanks for the reply, but I had already tried this, but the error remains the same as I described above.

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous,

     

    Modify your measure as below:

     

    Last_Month_price=
VAR _value=CALCULATE(MAX(fact_snapshot[last_amt]),PREVIOUSMONTH('Calendar'[Date]))
VAR _table=TOPN(50,fact_snapshot,_value,ASC)
Return
MAXX(_table,_value)

     

     

    Best Regards,
    Kelly

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