Forum Discussion

fhdalsafi's avatar
fhdalsafi
Frequent Visitor
5 years ago

TopN measure on multiple columns

Hello,

 

I am having a problem when trying to use TopN function.

 

I have this measure :

 

 

Class Hour =

Var _TESTTEST =
SUMMARIZE(
Data,
'Time Data'[Hour],
"TRXx",
CALCULATE([Trx Count],Data[PaymentType] in {"DB","PA"})
)
Var _Filter =
FILTER(
_TESTTEST,
[TRXx] <> 0
)

Var _TopNtest =
TOPN(
1,
_Filter,
[TRXx],
DESC
)

Var _Test =
SUMMARIZE(
Data,
Data[Bin],
Data[FullAccountNumber],
"PeakHour1",
_TopNtest
)
Return
COUNTROWS(_Test)
 
 
For more context, we use bin and fullaccountnumber columns to identify users, so my end goal is to get the hour for each user with highest transaction count.
  • the COUNTROWS function at the end is just to check if the measure works.
  • The Trx Count is just a measure we have to calculate the total transactions.
 
But when I test this measure on a Card visual for example I should get the number of rows that appear in the table I made. But I get an error indicating that the TopN function refers to multiple columns and it cannot be converted to a scalar value.
 
The problem was solved when I made a calculated table and added calculated column to it.
But is there a way that I can do it all in a measure and not use any type of calculated columns or tables?
 
If anything was unclear feel free to ask. Any help is appreciated.
 
Thanks,
Need Help

2 Replies