TopN ranking working in Dax Query view but not in Visual

[anwarbham]

Hi i am seeing some strange issue with the TopN function in power Bi

i have a dax formula 

    TOPN(
        10, // <-- This is N. Change to 5 for Top 5, 20 for Top 20, etc.
        VALUES(Fact_Table[EndDate_Time_Value]), -- <-- IMPORTANT: CHANGE THIS to the column you want to rank (e.g., VALUES('Product'[Product Name]))
          CALCULATE(
        SUM(Fact_Table[EndDate_Time_Value]),
        Fact_Table[stage] = "4B",
        Fact_Table[Produktion] = "OFF",
        Fact_Table[_Flag.1] = 1
    )
    )

this works in the Dax Query studio

but in the visual i get this message

my table is below

is this a bug ?

[FBergamaschi · Accepted solution]

Hello,

TOPN returns a table.
A measure cannot be a table of values but a single value. It works in query view as a DAX query returns a table. If you want to have it working in the DAX formula in Power BI you need to create a calculated table and not a measure. If you need a measure then you need to aggregate the values of this table. Hope I was clear, otherwise come back to me. You cann attach the pbix and I shall help but I need to know what you are trying to calculate.

Best

FB

[FBergamaschi]

Thanks for the kudos, in case it is ok like this can you mark it as a solution? Otherwise here I am Best, FB

[mark_endicott · Accepted solution]

@anwarbham - You are returning a table in TOPN and that cannot be produced as the output of a measure in a visual. 

You will be able to use this DAX to create a Calculated table. Or you could use the TOPN as a Variable (VAR) and then you can RETURN a calculation that aggregates or selects something from this table stored in the variable. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[mdaatifraza5556]

Hi @anwarbham 

This happens because TOPN returns a table 

If this answers your questions, kindly accept it as solution and give kudos.

[FBergamaschi · Accepted solution]

Hello,

TOPN returns a table.
A measure cannot be a table of values but a single value. It works in query view as a DAX query returns a table. If you want to have it working in the DAX formula in Power BI you need to create a calculated table and not a measure. If you need a measure then you need to aggregate the values of this table. Hope I was clear, otherwise come back to me. You cann attach the pbix and I shall help but I need to know what you are trying to calculate.

Best

FB