cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
naga449
Frequent Visitor

Adding Serial Number using DAX(Not in Transform data)

Hi Community,

 

Could you please help me with this,

 

I have a Dim Table with countries,

Country
India
Australia
South America
North America
Japan
Germany

I want to sort this in the column as it is in a table, so I need to add index or sequence numbers to this table USING DAX Only.

 

Please help with this.

 

Thanks 

Naga Karthik.

4 REPLIES 4
Stachu
Community Champion
Community Champion

you could use something like this:

Index = 
SWITCH('Countries'[Country],
"India",0,
"Australia",1,
"South America",2,
"North America",3,
"Japan",4,
"Germany",5
)

but althouth it will work, this is a bad practice. How is this table sourced, and what is the logic of the order? Or is it completely arbitrary?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

@Stachu 

 

You realize that this is totally unmaintainable unless... he has a list of all countries in the world?

Definitely, doing this in the Power Query/source should be the way to go, that's why I said it is a bad practice. But considering that some of the items are South America and North America I do not expect the list to be extremely long.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

daxer-almighty
Solution Sage
Solution Sage

You can't do this in DAX alone. Sorry, you're out of luck. The only sort that DAX obeys natively is the alphabetical order of strings and the natural order of numbers/date(time)s (date(time)s are numbers in disguise). To have a different sort than the mentioned ones you have to create a column and manually insert the numbers by which you want to sort the column. This is best done either in PQ (using Add Column) or outside the system in some ETL layer or in the source.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.