Edit Interactions not working correctly with 2 matrixes

Power BI
1

Hi everyone

I am having trouble editing interactions on a page where there are 2 matrixes.

What I am trying to achieve is to select a city, then select comparison cities for the data. I can do this in one matrix, but the cities are sorted in alphabetical order–not where the selected city is the first one in the visual.

image
image1222×441 25.1 KB

In this example, the Selected city is Shawnee–however, in the matrix, the city names are sorted alphabetically, so the comparison city is not the first city in the matrix.

I have another way of doing this with 2 matrixes, however, the selected city matrix is adding all of the amounts from the comparison cities.

image
image1310×572 30.2 KB

The selected city is coming from the Comparison city filter–which is a disconnected table, the cities for comparison are coming from the tbl1city table City Name Column.

I edited the interactions so that the Selected City matrix is not filtered by the City Name Slicer–however, when you hover over the Selected City Matrix–however, it shows the total of all cities in the Selected City matrix–not just the total for the Selected City for Allen.

My desired output is below–where I am combining the 2 matrixes and hiding some columns on one. This is a workaround that works–except the selected city Matrix is showing the total of all cities selected in the Selected Cities for comparison Matrix.

image
image1314×505 29.4 KB

If anyone has a way to do this without doing 2 matrixes where you can put the selected item from a disconnected table as the first item–that is my first choice, however, if this isn’t possible—and I have searched for several days and not finding a way with the native visual–I can do the 2 matrixes, I just need the total of the selected city to be just the selected cities amounts–not all of the cities selected.

Thank you all for your time. It is very much appreciated. The Power BI File is attached.

Edit Interactions in a Matrix.pbix (18.0 MB)

#Matrix, #edit interactions

1 Like
2

Hi @tjohnson ,
Thank you very much for posting your query in the forum.
To calculate the Tax Revenue measure for the selected city, I have created the following measure:

Tax Revenue Selected City = 
 CALCULATE(
    [Tax Revenue],
    TREATAS(
        VALUES( 'From City'[CityID] ),
            CitySalesTaxCollections[CityID]
    ),
    ALL( tbl1City )
)

We create a virtual relationship between the From City table and the CitySalesTaxCollections table through the [CityID] column using the TREATAS function and remove the filters by the tbl1City[CityID] column.

To do this, it is necessary that when creating the From City table we include the tbl1City[CityID] column:

From City = SUMMARIZECOLUMNS(
     tbl1City[CityID],
     tbl1City[Country],
     tbl1City[CityName],
     tbl1city[Latitude],
     tbl1City[Longitude]

I hope it can be of help to you.

Regards

Edit Interactions in a Matrix_JAFP.pbix (18.0 MB)

1 Like
3

Thank you so much, @jafernandezpuga This worked PERFECTLY!!! I very much appreciate your time. I will explore more the treatas function. I am self-taught and not a programmer, so have had to learn all of this on my own and the help with Enterprise DNA and this forum!! Have a great day!

1 Like