Matrix filtering first, aggregate after

[Anonymous]

Hi all,

I have a table which I visualize in the dashboard as a matrix visualization. In the table, there is a column (Max_version) that has 1 for the row that has the maximum value in another column (Version) and 0 for the rest of the rows. This is done according to the range of dates selected by a slicer. So if I have 10 rows of the same ID in the selected range of dates, I will have 1 row with 1 in Max_version and 9 rows with 0 in the Max_version column. I need this because I have to filter the table on this Max_version according to a selected range. In a tabular visualization everything works fine, but in the matrix visualization the problem is that the matrix does first the aggregation, and then the filtering. The matrix has aggregation on rows by City, Office, etc. What happens is that when the aggregation is done, I lose the information about the rows in Max_version that have 0 or 1 (If in the aggregated values there is a 1 and then "n" times the value 0, the result of the aggregation will be 1, so when it does the filtering, gets also values from rows that have Max_version = 0). Is there a way to avoid this? It would be enough that the matrix does first the filtering and then the aggregation, but I was not able to find a way to do it.

I hope the problem is clear. Thank you.

Here is a link to a test pbix file that reproduces the problem:

https://drive.google.com/file/d/1REYyXVSBEBKXxjvr45IMmaKnOfvC40i6/view?usp=sharing

Here is also an image for a quick look:

[amitchandak]

@Anonymous , Not very clear, you can use visual level filter on max version column =1

of create a measure , example

calculate(sum(Table[Value]), filter(Table, Table[Max version] =1))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak I updated the post with and image and the link to an example that reproduce the problem. Thank you.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please add the Version field to the filter pane of the matrix visual, then filter the field with Max_version.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for the short reply @amitchandak. It is pretty much all sensitive data, so I have to see if I can have something that I can share, that makes sense for the problem.

In the meantime (maybe this is more clear), I already used the visual filter level, and the problem is precisely on this feature. The visual filter is applied after the matrix has done the aggregation (probably I should have mentioned that I have some aggregation in the matrix by city, office, etc. The aggregations are on the rows). So the problem is that when it aggregates on the city for example the Max_version column will result for the city "any_city" with the value 1 (even if for that particular city there were multiple columns that had Max_version = 0. Once the aggregation is done it applies the filter so now when the filtering is done it is to late because the aggregation (done by the matrix automatically) already took into consideration the rows that have Max_version = 0. I will try to do some sample data.