Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
avendanof
Helper I
Helper I

Topn function using a Parameter

After having read this post https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...  I was wondering why this is not working.

 

I´ve first made a parameter Top N which has 

Top N = GENERATESERIES(0, 10, 1)
and 
 = SELECTEDVALUE('Top N'[Top N])
 
Then I´ve tried to use it in here:
 
5 mejores productos = TOPN([Valor Top N],summarize(DimProducto,DimProducto[EnglishProductName],"suma",sum(FactVentas[Ventas])),[suma])
 
But here is not working. 
 
When I try changing the first parameter of the function Topn to a value like this:
5 mejores productos = TOPN(5,summarize(DimProducto,DimProducto[EnglishProductName],"suma",sum(FactVentas[Ventas])),[suma])
This works like a charm, but if I insert a parameter instead of number five, it brings me very extrange things.
 
Then If I create a Mesure test like this:
Test = 5 
 
And I use it instead of the 5, like this:
 
5 mejores productos = TOPN(test,summarize(DimProducto,DimProducto[EnglishProductName],"suma",sum(FactVentas[Ventas])),[suma])
 
It works fine
 
Then if I change it to test = Valor Top N
 
Is still not working
 
I can´t understand this behaviour.
 
I´ve tried to change the type with format, int and still not works.
 
¿Is this an issue?
¿Can someone figure out why works like this?
 
Regards!!!
 
 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

 

 

// Careful here: this returns a table
// so it can't be a measure!!! Measures
// never return tables, only scalar
// values.

[5 mejores productos] =
var vProdCount = SELECTEDVALUE( 'Top N'[Top N] )
var vResult =
    TOPN(
        vProdCount,
        ADDCOLUMNS(
            DimProducto[EnglishProductName],
            "@Suma",
                CALCULATE(
                    FactVentas[Ventas]
                )
        ),
        [@Suma]
    )
RETURN 
    vResult

 

 

You can try the above... but this is not a measure. The code returns a table (as does yours), so I'm not sure what you're trying to achieve with this. If no selection has been made in the Top N table, then this will return nothing. And for such a selection to occur, the parameter table must be put in a visual. But the code above, as I said, returns a table, so you'll not be able to use it, even if there's been a selection made in the slicer.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

@avendanof 

 

To be frank, I don't understand why the version with SELECTEDVALUE under TOPN would not work, either. I would say it does work but we probably don't fully understand why it returns what it returns. Please note that SELECTEDVALUE will return BLANK (=0) if there are no selections made in the slicer or if there are many items selected. And as I already said, you're trying to return a table and this will never work on the PBI canvas. Even more, you can't expect that such a calculated table will constantly re-calc when you change the selections in the slicer. Calculated tables are static by design and no selection on the canvas can affect them. So, if you think that your table should re-calc and SELECTEDVALUE will return the selection from the slicer... well, you are very much mistaken.

avendanof
Helper I
Helper I

Thanks a lot, actually, yes, a table is what I expect. Ehat I don´t actually understand is why I need to pass throgh a variable instead of using the selectedvalue in the topn directly.

Regards!!! and have a fine day.

 

Anonymous
Not applicable

 

 

// Careful here: this returns a table
// so it can't be a measure!!! Measures
// never return tables, only scalar
// values.

[5 mejores productos] =
var vProdCount = SELECTEDVALUE( 'Top N'[Top N] )
var vResult =
    TOPN(
        vProdCount,
        ADDCOLUMNS(
            DimProducto[EnglishProductName],
            "@Suma",
                CALCULATE(
                    FactVentas[Ventas]
                )
        ),
        [@Suma]
    )
RETURN 
    vResult

 

 

You can try the above... but this is not a measure. The code returns a table (as does yours), so I'm not sure what you're trying to achieve with this. If no selection has been made in the Top N table, then this will return nothing. And for such a selection to occur, the parameter table must be put in a visual. But the code above, as I said, returns a table, so you'll not be able to use it, even if there's been a selection made in the slicer.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.