TOPN with previous month

[Anonymous]

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.

[v-kelly-msft · Accepted solution]

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!

[amitchandak]

@Anonymous , Try like

all(Fact_snapshot)

or

allselected(Fact_snapshot)

[Anonymous]

Hi @amitchandak ,

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

[amitchandak]

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

[Anonymous]

Hi @amitchandak , 

Sorry but it not possible.

But the measure "CALCULATE(MAX(fact_snapshot[last_amt]),PREVIOUSMONTH('Calendar'[Date]))" already return more than 1000000 rows. I just wont to show the TOP50 of this measure.