Index Direct Query

Hi Saxon10 ,

In Direct query you cannot add any additional columns to your model, not sure how you have your setup but if you don't have any other ID field or detail field that allows you to have the distinction between the elements you can't have this directly.

If you can edit the SQL you can has lbendlin  told edit in Power Query in order to add that column, however if you can't edit the Direct Query one optionmaybve it's to create an table using the generate series. Adding this table will create an composite model that has a direct query and import table in this case you can do the following:

• Generate table with the count of rows of the ID you need to count:

ID Table = 
GENERATESERIES (
    1,
    MAXX (
        SUMMARIZE (
            'FACT',
            'FACT'[Type],
            "TOTALROWS", COUNTROWS ( 'FACT' )
        ),
        [TOTALROWS]
    ),
    1
)

• Create a Measure:

Ranking =
IF (
    SELECTEDVALUE ( 'ID Table'[Value] )
        <= COUNTROWS (
            FILTER ( ALLSELECTED ( 'FACT' ), 'FACT'[Type] = MAX ( 'FACT'[Type] ) )
        ),
    SELECTEDVALUE ( 'ID Table'[Value] )
)

• Create a table with the ID, the column from the Raking table and the Ranking metric

This creates a duplication of the number of columns but you can then hide one of those columns.

What are you trying to achieve with that index? Can you add the index in the SQL source?