cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
avendanof
Frequent Visitor

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
daxer
Solution Sage
Solution Sage

 

 

// 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
daxer
Solution Sage
Solution Sage

@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
Frequent Visitor

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.

 

daxer
Solution Sage
Solution Sage

 

 

// 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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors