No doubt that one of the most frequently used functions in Excel is the Sort function.
This feature has 2 constants: A-Z and Z-A i.e. Ascending and Descending Sort.
We sort our data by certain field in pretty much all tables.
Have you ever tried to get this sorting action done with a formula?
WHAT DOES IT DO
You can easily sort columns or rows by the sort order you want in a different field from your mail table with the SORT function that is one of the dynamic array functions that are recently released after Office 365 .
SYNTAX
=SORT(array,[sort_index],[sort_order],[by_col])
There are 4 arguments in the function.
The first one is required, the next 3 arguments are optional.
Now, let’s take a look at what these arguments mean, i.e. what the function wants from us and what we will give it.
Argument
Description
array
Required
The range, or array to sort
[sort_index]
Optional
A number indicating the row or column to sort by
[sort_order]
Optional
A number indicating the desired sort order; 1 for ascending order (default), -1 for descending order
[by_col]
Optional
A logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column
USE OF THE FUNCTION
Choose the fields you want from your table and state by which column and order you will sort.
When you choose a field for the array argument only, the data is sort based on the Ascending order.
If multiple columns are selected in the array argument, the column by which the sorting will be done is chosen.
You can choose the Descending order in the sort_order argument if you want. If you don’ choose it, the sort is always Ascending.
In the by_col argument, you can sort by column i.e. from left to right. If not chosen, the sorting will always be from top to bottom.
Let’s have an example and see what happens when we choose the array argument only.
Now, let’s choose multiple columns and state the sort_index argument as well.
Since we didn’t use the sort_order argument in the formula below, prices are sorted from the lower to the higher. Now, let’s use the sort_order argument in the same function and see our data sorted from the higher to the lower.
Lastly, let’s sort the data from left to right and now from top to bottom.
We will be talking about the other New Dynamic Array functions in our next articles.
And then we will be able to get things done way easier by using these new functions together. LONG LIVE THE NEW DYNAMIC ARRAY FUNCTIONS!
See you in other articles, bye. 🙋🏻♂️
You can share this article and help a lot of people get informed as well. 👍🏻
Hello, here we are with the Power BI March update! I realize how fast the months go by with the new updates. This month, we have some long-waited and nice updates. In this sense, Power BI users’ opinions were taken into consideration. If you have all buckled up, let’s start!
1- New Action Alert
The most eye-catching feature in the Power BI March updates! New action options are available for the buttons we add for more interaction and a better application image:
Page Navigation
Drill through
We tried to find our way with the Page Navigation action to this day: by adding a bookmark with the most unfiltered version of the other page. Microsoft found out that we solved it like the and stepped in and offered this as an action option to do this the legal way. I can spend all my applause on this feature.
Page navigation
The second one is one of the actions we wanted to see in the scenes. We were so happy about even the “Right-click to use the drill through.” text! -Because normally we had to go to charts and right-click to find that feature!- That’s why we are extremely happy about it.
Activating the Drill through feature through the “See table details” button.
When we choose the Drill through as action, we can print the chosen one with conditional formatting and this helps a lot in terms of having an image that puts people into action. Also, you can write tooltip texts for when its active/passive.
You can print the selected value by using conditional formatting for the text in the button and make it visible by writing any text you want to the Enabled Tooltip area.
You can write what you want to do into the tooltip field in order to activate the button.
2-Multi-column sort for tables
Feels like it was just yesterday that you were crying “But why? We need this feature!” and I was saying “I know, I know, it is so ridiculous but it is what it is!” And now we finally got this update! To add more columns to the sort order, Shift + click the column header you would like to add next in the sort order.
When you click a column title with the SHIFT key, it accepts that column as sorting measure as well.
3- Dual axis for line chart
It was our subjects in recent trainings! Now we can add a second axis that allows us to draw two trends with different ranges along the same X-axis progression. We can compare two different units in the same graphic at the same time. Like Sales Amount&Sales Total, Sales Tex&Sales Total…
4-Filter Pane Search
It was a feature that was available in web but not in desktop. Now it is available for desktop too. You can search the filter you want in the search area. This features is activates as default. You can turn this feature on or off in the reports settings of the Options dialog.
5-Updates to decomposition tree
Some people were very happy when the decomposition tree came us default to Power BI! The decomposition tree now supports modifying the maximum bars shown per level. The default is 10 and users can select values between 3-30. Setting a low number is particularly handy if you don’t want the decomposition tree to take up too much space on the canvas. That way it’s more useful.
6-New DAX function: COALESCE
A new DAX Function enters our lives with the Power BI March updates. The COALESCE function returns the first expression that does not evaluate to BLANK. So, what the hell does this mean? There are already a lot of functions as “That does not evaluate to BLANK”… They syntax of this function is:
COALESCE(, [, ]…)
It goes like that step by step and shows us the non-blank one. As outcome, it returns us a scalar expression. We can use it in an example like that:
When we do a calculated and show in on card, we don’t want the blank expressions to be visible. We want something like 0,1,100,”-“. For this we use the IF(ISBLANK(…) expression. Right at this point we can use this function.
Product of Esri -the most known map and cartography company of the world: ArcGIS. We have been using its maps as default anyways for a long time and knew that more detailed features would come. Most features are available for Premium and some can be used in Pro. First of all, let me show you the location of ArcGIS maps.
ArcGIS location
When you click here, there are 3 types of connections:
ArcGIS Enterprise
ArcGIS Online
Standart
The ArcGIS Enterprise and ArcGIS Online options are for users who have the premium app subscription and provides all the capabilities of Standard and extra capabilities, including additional geocoding, technical support, and access to mapping reference layers, and more. The Standard option is free and provides basic mapping capabilities. I leave the features below for you to check them.
Multiple Reference Layer
All premium app users can now add multiple reference layers to a single map visualization within Power BI. A reference layer is information represented on a map. It adds context to your operational business data. For example, let’s say you have mapped your store locations in Power BI. You can now overlay it against reference layers such as income, age, competitor locations or other demographics to gain valuable insights. You can add data and layers that are published and shared online by the ArcGIS community as well as layers from your ArcGIS Online or ArcGIS Enterprise organization.
New table of contents
A new table of contents that will help all ArcGIS Maps for Power BI users (free and premium) better visualize their data on a map has been added. Now, when you drag data to a location field well and see it on a map, you can also see a table of contents that lists all the layers on the map and shows the features represented by the layers. This allows your report viewers to quickly understand the data that they are seeing.
8-Data Sources
Is an update without a new data source an update at all?
HIVE LLAP: This connector provides both Import and Direct Query capabilities and the ability to specify Thrift Transport Protocol as ‘Standard’ or ‘HTTP’.
Cognite: The Cognite Power BI connector enables data consumers to read, analyze, and present data from Cognite Data Fusion (CDF).
We have shared our favorite updates for this month. You can click here for our other articles about Power BI. Click here to download the latest version of Power BI Desktop.
