TopN measure on multiple columns

[fhdalsafi]

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,

[Ailsa-msft]

Hi @fhdalsafi 

TOPN can return results in batches, returning all the first N rows of records that meet the conditions from a table. Its role is equivalent to a filter not a measure or column to calculate a value .

You can learn more through the link below .

https://docs.microsoft.com/en-us/dax/topn-function-dax

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@fhdalsafi , TOPN returns table that act as filter, Not meausre

Usages example

calculate([Measure], TOPN( 1,allselected(Table[Column]),[Measure],DESC), values(Table[column])))

refer if needed

TOPN: https://www.youtube.com/watch?v=QIVEFp-QiOk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42