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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors