How to write measure to select top 10 rows from a table

[wokka]

I come from a SQL background, and recently I wanted to do something simple ( or so I thought ) - use a custom dimension to display the top 10 rows from an existing dimension table ( or any user table ).

In SQL I would write this as :   Select TOP 10  [column1], [column2]  from  Table_Test1

So I want to have this as a custom measure so I can drag and drop onto the desktop in powerbi and tweak the code as needed to use for any table please? Later I want to pass a table name as a parameter, so i can use it to display data from any table. 

Thanks in advance 

[v-sgandrathi]

Hi @wokka,

Thankyou  @Jihwan_Kim for your reply on the issue.

Has your issue been resolved? If the response provided by the community member addressed your concern, kindly confirm.

Marking it as Accept Answer and give us Kudos if you found it helpful allows us to ensure that the solutions shared are valuable for the entire community.

If you have any further questions, feel free to reach out!

Thank you for your cooperation!

[v-sgandrathi]

Hi  @wokka,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @wokka,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I tried to use filed parameter for dynamically select dimensions in the visual.

Use report readers to change visuals (preview) - Power BI | Microsoft Learn

I used WINDOW DAX function to limit top 2 (you can try to change to any number in the measure, or you can create variable for that specific number to make it more dynamic). When I tried to write measure by using my sample, it is adding conditions into the measure one by one, but please provide your sample and then I can try to look into it to come up with more dynamic way.

WINDOW function (DAX) - DAX | Microsoft Learn

Sales: = 
SUM( sales_fact[sales] )

top 2 sales: = 
VAR _t1 =
    SUMMARIZE ( ALL ( sales_fact ), area_dim[country] )
VAR _t2 =
    SUMMARIZE ( ALL ( sales_fact ), area_dim[country], area_dim[region] )
VAR _t3 =
    SUMMARIZE (
        ALL ( sales_fact ),
        area_dim[country],
        area_dim[region],
        area_dim[site]
    )
RETURN
    SWITCH (
        TRUE (),
        CALCULATE ( MAX ( prm_area[prm_area Order] ), ALLSELECTED ( prm_area ) ) = 0,
            CALCULATE (
                SUM ( sales_fact[sales] ),
                KEEPFILTERS (
                    WINDOW (
                        1,
                        ABS,
                        2,
                        ABS,
                        _t1,
                        ORDERBY ( CALCULATE ( SUM ( sales_fact[sales] ) ), DESC )
                    )
                )
            ),
        CALCULATE ( MAX ( prm_area[prm_area Order] ), ALLSELECTED ( prm_area ) ) = 1,
            CALCULATE (
                SUM ( sales_fact[sales] ),
                KEEPFILTERS (
                    WINDOW (
                        1,
                        ABS,
                        2,
                        ABS,
                        _t2,
                        ORDERBY ( CALCULATE ( SUM ( sales_fact[sales] ) ), DESC )
                    )
                )
            ),
        CALCULATE ( MAX ( prm_area[prm_area Order] ), ALLSELECTED ( prm_area ) ) = 2,
            CALCULATE (
                SUM ( sales_fact[sales] ),
                KEEPFILTERS (
                    WINDOW (
                        1,
                        ABS,
                        2,
                        ABS,
                        _t3,
                        ORDERBY ( CALCULATE ( SUM ( sales_fact[sales] ) ), DESC )
                    )
                )
            )
    )

[wokka]

Hi, Thanks I will have a look.

[kushanNa]

Hi @wokka 

you can use a calculated table to create a top 10 table 

sample code :

tt = TOPN(10, 'Table','Table'[Column1], DESC)

[wokka]

Thanks, although I get this error when I have formatted the output as text :

Does this mean I need to list all columns in the table, or should it work with just one column out of the 15 columns in the table? 

[kushanNa]

Hi @wokka 

The query I had given is a calculated table query , so you have to use it as a table ,

eg: 

[wokka]

OK, we dont have access to power query, I probably should have said I wanted to run it direct from the desktop as a custom measure.

[kushanNa]

oh okay , have you tried to use Visual filter Top N option ?

[wokka]

Hi, I really want to write it purely in DAX if I can. 

[kushanNa]

hmm i feel like you will need to use visual level filtering at some point 

here is a hybrid solution (DAX & Visual filtering) comes to my mind 

create the following measure to rank the product 

eg:

Product Rank by Sales = 
RANKX(
    ALLSELECTED(SalesData[Product]), 
    CALCULATE(SUM(SalesData[TotalSales])), 
    , 
    DESC, 
    DENSE
)

and create another measure to decide the Top N

Show in Top n = 
IF(
    [Product Rank by Sales] <= 2,  -- ← Change 2 to any N
    1,
    0
)

and use visual level filter to activate the ranking drag the measure "show in top n " to the visual filter panel and filter it to show only 1 active rows 

eg: this show top 2 rows