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.
Dear Friends,
I have table as a below,I would be happy if you guide me how I can add the calculated index column to the table a New column by a DAX formula.
Thank you,
Myti
Solved! Go to Solution.
Try to create a calculated column in DAX.
index = RANKX ( FILTER ( yourTable, EARLIER ( yourTable[CC] ) = yourTable[CC] && EARLIER ( yourTable[Type] ) = yourTable[Type] && yourTable[Cluster] = yourTable[Cluster] && EARLIER ( yourTable[Status] ) = yourTable[Status] ), yourTable[Avg-Position], , ASC )
I'm trying to do something similar, but instead of "status", I have a date column and I want the index to calculate based on ascending date. How do I add that step? Thanks
Hello everyone,
I need to add a simple 0 based index to this GENERATESERIES
Index from 0 to 36.
I need this index to be able to join with another table.
Anyone can help me with this ?
Found the solution, the problem was I'm generating a dynamic table with GENERATESERIES but could not join it with another table in my model. I needed a common column to be able to join.
The solution is RANKX, since these are dates and they are in the proper order I added a column with RANKX.
New column
Sample Gen Date Series =
GENERATESERIES(
DATE(2020,1,1)
,DATE(2023,2,2)
, 31)
Then RANKX to generate the index,
Index = RANKX( ALL('Sample Gen Date Series'),'Sample Gen Date Series'[Value].[Date],,ASC)
because I needed a base 0 index I made a New Column = 'Sample Gen Date Series'[Index] - 1
That's it
Eric
Hello.
Would you please tell me how you create the column Average Position? i need something like that.
Thanks!
Can you explain the mechanics behind that Index column? If I am reading it correctly, you start at 1 and increment until the "Status" changes and then you start back at 1 and so on.
My feelings are that you would have better success implementing this in "M" rather than DAX.
Thanks @Greg_Deckler for your quick reply.
My idea is that to add index based on value of Avg-Position.Changing is not only based on "status".It is based on the the four first columns.
Thank you
Try the following steps
1. Go to the editQuery of the table.
2. Go to the Add Column Tab
3. Add Index Column
4. Rename that column as ColIndex
5. Close the EditQuery
6. Right Click Your table
7. Click on New Column
8. Paste the following code
DesiredIndex = CALCULATE(COUNT([ColIndex]), ALL('Table2'),
FILTER('Table2', [ColIndex] <=EARLIER([ColIndex])),
FILTER('Table2', [CC]=EARLIER([CC])),
FILTER('Table2', [Type]=EARLIER([Type])),
FILTER('Table2', [Cluster]=EARLIER([Cluster])),
FILTER('Table2', [Status]=EARLIER([Status]))
)
You will get the result you want.
If this works for you please accept this as solution and also give KUDOS.
Cheers
CheenuSing
Thanks to you for your solution.
The problem is that the table I have is a summerised table that were creatd by DAX formulah in data view section.SO I dont have access to my table in query editor.
As @Greg_Deckler advised ,I am thinking to recreate the the tables that I created in Data view in query editor section by M language.
Thank you,
Regards
Medi
Try to create a calculated column in DAX.
index = RANKX ( FILTER ( yourTable, EARLIER ( yourTable[CC] ) = yourTable[CC] && EARLIER ( yourTable[Type] ) = yourTable[Type] && yourTable[Cluster] = yourTable[Cluster] && EARLIER ( yourTable[Status] ) = yourTable[Status] ), yourTable[Avg-Position], , ASC )
How would you do this if you wanted to do a calculated index for "status" in chronological order? So, if there were dates associated with the different rows, Index = 1 for the earliest date of Low status and = 3 for the latest date of Low status.
I figured out my solution:
Thanks so much!
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |