DAX selecting and displaying the max value of all selected records

Question

Problem

I'm trying to calculate and display the maximum value of all selected rows alongside their actual values in a table in Power BI. When I try to do this with the measure MaxSelectedSales = MAXX(ALLSELECTED(FactSales), FactSales[Value]), the maximum value ends up being repeated, like this:

If I add additional dimensions to the output, even more rows appear.

What I want to see is just the selected rows in the fact table, without the blank values. (i.e., only four rows would be displayed for SaleId 1 through 4).

Does anyone know how I can achieve my goal with the data model shown below?

Details

I've configured the following model.

The DimMarket and DimSubMarket tables have two rows each, you can see their names above. The FactSales table looks like this:

+--------+----------+-------------+-------+-----------+
| SaleId | MarketId | SubMarketId | Value | IsCurrent |
+--------+----------+-------------+-------+-----------+
| 1      | 1        | 1           | 100   | true      |
+--------+----------+-------------+-------+-----------+
| 2      | 2        | 1           | 50    | true      |
+--------+----------+-------------+-------+-----------+
| 3      | 1        | 2           | 60    | true      |
+--------+----------+-------------+-------+-----------+
| 4      | 2        | 2           | 140   | true      |
+--------+----------+-------------+-------+-----------+
| 5      | 1        | 1           | 30    | false     |
+--------+----------+-------------+-------+-----------+
| 6      | 2        | 2           | 20    | false     |
+--------+----------+-------------+-------+-----------+
| 7      | 1        | 1           | 90    | false     |
+--------+----------+-------------+-------+-----------+
| 8      | 2        | 2           | 200   | false     |
+--------+----------+-------------+-------+-----------+

In the table output, I've filtered FactSales to only include rows where IsCurrent = true by setting a visual level filter.

Edit

I've found a solution to my problem, but I'm slightly concerned with query performance. Although, on my current dataset, things seem to perform fairly well.

MaxSelectedSales = 
MAXX(
    FILTER(
        SELECTCOLUMNS(
            ALLSELECTED(FactSales),
            "id", FactSales[SaleId],
            "max", MAXX(ALLSELECTED(FactSales), FactSales[Value])
        ),
        [id] = MAX(FactSales[SaleId])
    ),
    [max]
)

If I understand this correctly, for every row in the output, this measure will calculate the maximum value across all selected FactSales rows, set it to a column named max and then filter the table so that only the current FactSales[SaleId] is selected. The performance hit comes from the fact that MAX needs to be executed for every row in the output and a full table scan would be done when that occurs.

Strawberryshrub Strawberryshrub · Accepted Answer · 2020-02-10T05:24:16

Your max value (the measure) is a scalar value (a single value only). If you put a scalar value in a table with the other records, the value just get repeated. In general mixing scalar values and records (tables) does not really bring any benefit.

Measures like yours can be better displayed in a KPI or Multi KPI visual (normally with the year, that you get the max value per year).

If you just want to display the max value of selected rows (for example a filter in your table), use this measure:

Max Value = MAX(FactSales[Value])

This way all filter which are applied are considered in the measures calculation.

Here is a sample:

DAX selecting and displaying the max value of all selected records

1 Answers