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.
Hello!
I'm creating a summarized table and I need to have an index number or row number. Search results lead to having index number using the Power Query but in my case, I need to have a summarized table.
My summarized table is just like this:
Category | Index (I don't know how to have this) |
A | |
B | |
C | |
D | |
E |
I tried this but I only get the same result:
Any help is appreciated. Thank you!
Solved! Go to Solution.
Please try this column expression.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This would be done more easily in the query editor, but here is how to do it in a DAX table. There probably is a way to do it in one table variable, but I used two to make it easier. Replace with your actual table/column names.
New Table =
VAR table1 =
ADDCOLUMNS (
SUMMARIZE (
Category,
Category[Category],
Category[Group]
),
"merge",
Category[Group] & Category[Category]
)
VAR table2 =
ADDCOLUMNS (
SUMMARIZE (
Category,
Category[Category],
Category[Group]
),
"Rank",
VAR thismerge = Category[Group] & Category[Category]
RETURN
COUNTROWS (
FILTER (
table1,
[merge] < thismerge
)
) + 1
)
RETURN
table2
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hello! How do you this in a DAX column? Thank you! I'm gonna use this for my hierarchy. Much thanks again!
Not sure what you mean by summarized table. Are you making a DAX table with SUMMARIZE? Or do you mean a table visual with the Category and Group columns? Please clarify.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hello @mahoneypat !
Yes, I'm making a DAX table using the SUMMARIZE function. The above excel screenshot that I sent is my expected outcome from my DAX table. Thanks!
Please try this column expression.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Btw @mahoneypat , how do I add this during the summarized formulas? Also, can I add index numbers based on group?
For example: (for some reason, I can't attach a table so I provided a sample screeshot)
Thanks in advance!
Thank you!
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 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |