From the course: Power BI Weekly

Display the top of a symmetric matrix - Power BI Tutorial

From the course: Power BI Weekly

Start my 1-month free trial Buy for my team

Display the top of a symmetric matrix

“

- [Instructor] If we create a symmetrical matrix in Power BI or any other tool, the top part of the matrix is the exact same as the bottom part, but transposed along the axis that runs through the middle. We can see this in our distance calculation examples between the origin and destination pairs. It doesn't matter what order that the order or destination go in. We can actually reverse the destination and origin and get the same numbers. Let's use this logic to only display the top part of the matrix. Notice that we're adding the combination of rows and columns together to pair them up within our matrix view. For this example, to only see the top part of the matrix, this is a calculation group. So we're going to modify the formula for the calculation item associated with this calculation group. We can do this by navigating to the Model view to make our changes. We're going to expand some of the levels. If you're going back into the calculation group, you want to expand each of these levels until you reach the calculation item that you want to edit. We'll then click into our calculation item formula. We're going to set our current result equal to the VAR result. I'm also going to calculate another part of this formula that's going to use the MAX to reference, we're going to call this city_label, and we'll refer to the MAX of the City Coordinates. We can then update a result formula to use the city_label to make it a bit more efficient. Now we need to add another return statement that actually returns the results of our calculation item. We'll set this up using IF conditional logic. So we want to use the alphabetical order of the labels as a way to sort them. So I'm going to refer to the selected measure name by using its eponymous function. And we use these selected measure names in different, more limited docs formulas, but where we can use them are often very impactful. So we want to say the SELECTEDMEASURENAME is greater than or equal to our city_label, that is we already calculated within this formula, then we'll return the result, otherwise we'll return a blank. So what we're doing here is we're saying that if the name of our measure, which is the city that is the destination, comes after the city_label within our result, we want to essentially use this to control which part of the matrix we see. Let's confirm our calculations, and we'll navigate back to our Report view. So now we only see the top part of the matrix. What we've done here is we've removed duplicated pairs of cities that we're calculating the distances between. We can also display only the bottom part by changing the inequality and the return statement to go the other way. We'll say less than or equal to the city_label. And now what we see here is that if this label comes after the city name in the column, it will display the distance, otherwise it returns a blank. So we flipped the way that the symmetrical matrix appears. This often comes down to personal preference, and I actually like to see all the combinations of the calculations between different pairs, but sometimes being able to pair it down will make it easier to read, especially when we're looking at a symmetrical matrix with as many cities in this example.

Contents