Index Direct Query

[Saxon10]

The source of data came from SQL server and I am not able to change the data or modify any new columns in SQL query.

How can I create a Index in direct query method. 

I can't use the power query options in direct query method and its not supporting use it. 

(From Index 0, From Index 1 and Customs)

Is there any measure formula able to create a Index in direct query method?

Could you please assist me. 

[MFelix]

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.

•  

[Saxon10]

@MFelix,

could you able to share the sample out file because I am receving error message. 

[Saxon10]

Thanks for your reply and sorry for the late response. Some reason I haven't received the email notification so I missed the new message.

Thanks for finding the solution to get the unique count 

I will test your solution and update the feedback to you. 

[lbendlin]

In Direct query you cannot add any additional columns to your model

Well...  you can if you add a local model and the column is computed within the row.

[MFelix]

You are correct, what I meant was that keeping the model has direct query you cannot make those type of changes. Sorry for the error. 

[lbendlin]

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

[Saxon10]

@lbendlin,

Thanks for your reply. I am trying to countif and countifs based on the index column. Please refer the snapshot 

 

I am using the following formula in Excel COUNTIF($A$2:$A2,A2) and COUNTIFS($A2:$A2,A2,$B$2:$B2,B2). I like to apply the same login in power bi but unable to achieve without index column.

Is that's anyway I can apply the above mentioned formula in power bi without index column?

I am unable to add index in SQL source. 

Can you assist me. 

[lbendlin]

There are many ways to do that - in Power Query you can do grouping with group type local, or in Power BI you can use the new windowsing functions  INDEX/OFFSET etc.

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Saxon10]

@lbendlin,

Thanks for your reply and sorry for the late response. Some reason I haven't received the email notification so I missed the new message.

Thanks for sharing new power bi functionality and your suggestion.