Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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:
ID Table =
GENERATESERIES (
1,
MAXX (
SUMMARIZE (
'FACT',
'FACT'[Type],
"TOTALROWS", COUNTROWS ( 'FACT' )
),
[TOTALROWS]
),
1
)
Ranking =
IF (
SELECTEDVALUE ( 'ID Table'[Value] )
<= COUNTROWS (
FILTER ( ALLSELECTED ( 'FACT' ), 'FACT'[Type] = MAX ( 'FACT'[Type] ) )
),
SELECTEDVALUE ( 'ID Table'[Value] )
)
This creates a duplication of the number of columns but you can then hide one of those columns.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêscould you able to share the sample out file because I am receving error message.
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.
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.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsWhat are you trying to achieve with that index? Can you add the index in the SQL source?
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |