How do I create a global max value that references another measure?

[pacificnwp]

I found a very similar post regarding visitor counts, but I can't seem to make the solution work for my report.

I have a flattened sales data set (no tables), with each sales rep having a row of data for each quarter, i..e. with 10 quarters of data a rep would have 10 rows in the data set.  I created a measure "Quarters to Date", which counts the number of quarters - see calc below.  My data set is filtered back to 2023, so as of today there are 10 quarters in the data set, although some sales reps that quit will have less than 10.

Quarters to Date =

CALCULATE(

    COUNTROWS(

        FILTER(

            'Sales',

            'Sales'[Quarter]

        )

    )

)

I want to create an additional column that takes the max amount of that measure.  For example, there could be 20 sales reps with varying quarters, but the ones that have been here every quarter will have 10 quarters under their belt.  I want a max field that will then show 10 on every row.  I plan to then use that field to filter the results for sales reps that have "Quarters to Date" that equals this max value, which will essentially give me the reps that have been active.  

This would be easier if we had a sales rep term date, but we do not, so this was my best attempt at filtering the results for sales reps that have activity every quarter.

[v-lgarikapat]

Hi @pacificnwp ,

Thanks for reaching out to the Microsoft fabric community forum.

@MasonMA , @Ashish_Mathur , @GeraldGEmerick 

Thanks for your prompt response

Hi @pacificnwp 

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,
Lakshmi.

[v-lgarikapat]

Hi @pacificnwp ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,
Lakshmi.

[v-lgarikapat]

Hi @pacificnwp ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,
Lakshmi.

[MasonMA]

@pacificnwp 

Hi,

COUNTROWS(FILTER('Sales', 'Sales'[Quarter])) is not really filtering anything, it just counts all rows in the current filter context. As a measure, it cannot be used directly in a calculated column because measures are evaluated in filter context, not row context.

The correct approach would be creating a Measure to count the number of rows for a sales rep under the current filter contex

Measure1 =
COUNTROWS('Sales')

another Measure to return the maximum number of quarters any rep has

Measure2 =
MAXX(
VALUES('Sales'[SalesRep]),
[Measure1]
)

Then use a flag measure as visual filter so that shows only rows where Is Active Rep = 1. 

IsActiveRep =
IF([Measure1] = [Measure2], 1, 0)

[pacificnwp]

Thanks for your suggested solution. I created Measure1 and 2 as suggested above, however they are both generating the same results, whereas I would expect measure2 to display the max value on every line, even if that sales rep had a lower number.

[MasonMA]

This should be able to ensure the count is done per rep, ignoring other filters like quarter.

Measure1 =
CALCULATE(
    COUNTROWS('Sales'),
    ALLEXCEPT('Sales', 'Sales'[SalesRep])
)

For Measure2 to calculate Max Quarter my best guess is to use the approach GeraldGEmerick suggested but with VALUE() since it's a single column. 

Measure2 =
MAXX(
    ADDCOLUMNS(
        VALUES('Sales'[SalesRep]),
        "RepQuarters", [Measure1])
    ),
    [RepQuarters]
)

Please share a small sample of your flattened data just a few rows for 2–3 reps? That way other users can test the measures directly and show you the expected outputs row by row.

[pacificnwp]

Below is a screenshot of a subset of sample data.  My report filters out the orange rows (only keeping calc schema = main, and 2023 forward), but I included them here for visibility.  It seems some of the DAX formulas I've tried pick some of those rows up, but I don't want them included in the results.

[MasonMA · Accepted solution]

@pacificnwp 

Hi, there would be two solutions.

One with DAX, first create a Calculated Column

QuarterKey = 'Sales'[Year] * 10 + 'Sales'[Quarter]

then create 3 DAX Measures

Quarters to Date = 
CALCULATE (
    DISTINCTCOUNT ( 'sales'[QuarterKey] ),
    ALLEXCEPT ( 'sales', 'sales'[Name] )
)

MaxQuarters = 
MAXX(
    ADDCOLUMNS(
        VALUES('Sales'[Name]),
        "RepQuarters", CALCULATE(
            DISTINCTCOUNT('Sales'[QuarterKey]),
            ALL('Sales')
        )
    ),
    [RepQuarters]
)

IsActive = 
IF ( [Quarters to Date] = [MaxQuarters], 1, 0 )

apply them on table and you will see 'Mike Smith' should be filtered out. 

Or in Power Query, you can use below logic to filter out the above two person as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZSxCoMwFEV/RTI7tMnzDzoJTg4dxCGV0AZjUqr/T1NQCr7W5A5ClHPJeeGarhO19qa4BCNK0T7NYLWz8xJfzvGRJynX5aStF32ZxUuQVyBPAK9AfwX6K9Bfgf4E+hPoT6A/gf4V6F/t/Rs7mqKd7PLYJ+SfhmYl8D0UnCAowZqalfgsXQjjTQ8jlsL2waZnLU8mWM+zEtgcW9ex82J/yNU6V9TBmzl+bbTXd/NamV91TOMSw0EZysdZBdM4619eBNgBGJfV7hhnnUvjgDvYG3axHuPbvQqcPbuK0/h33P4N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Role Title" = _t, Area = _t, Year = _t, Quarter = _t, #"Calc Schema" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    Custom1 = Table.SelectRows(#"Changed Type", each  [Calc Schema] = "main" and [Year] >= 2023),

    // Step 2: keep unique Rep/Quarter Year
    UniqueRepQuarter = Table.Distinct( Table.SelectColumns(Custom1, {"Name", "Year","Quarter"}) ),
    // Step 3: group by rep
    Grouped = Table.Group(
        UniqueRepQuarter,
        {"Name"},
        {{"QuarterCount", each Table.RowCount(_), Int64.Type}}
    ),
    // Step 4: find max quarters
    MaxQuarters = List.Max(Grouped[QuarterCount]),

    // Step 5: keep only active reps
    ActiveReps = Table.SelectRows(Grouped, each [QuarterCount] = MaxQuarters),
    // Step 6: re-join to original filtered data
    Result = Table.NestedJoin(
        Custom1,
        {"Name"},
        ActiveReps,
        {"Name"},
        "Active",
        JoinKind.Inner
    ),
    Expanded = Table.ExpandTableColumn(Result, "Active", {"QuarterCount"})
in
    Expanded

Calc Schema and Year can be adjusted at this step, 

Custom1 = Table.SelectRows(#"Changed Type", each [Calc Schema] = "main" and [Year] >= 2023)

if i'm using the above conditions i'll have below result at 'ActiveRep' step.

[pacificnwp]

Apologies it took a while for me to get back to this.  I followed your strategy (calculated column and two DAX measures).  The MaxQuarters is almost accurate, but it's factoring in quarters outside of my date range.  

My dataset goes back to 2021, however I have a page-filter for 2023, 2024, and 2025.  There are other filters as well, as well as filter context at play (Sales Rep/Location/Title)... how do I get it to keep the same general calculation, but also consider the year filter?

[pacificnwp]

Thanks! I realized that I cannot create a column due to my connection type (direct connection), but I'll see if our data team can add that column to the model for me.  I may have to set this aside and come back to it, as I prefer the simplicity of the first solution (and therefore my ability to maintain/modify it).

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result clearly.  Also, share data in a format that can be pasted in an MS Excel file.

[GeraldGEmerick]

@pacificnwp You should be able to get the maximum via something like the following:

Max Quarters to Date = 
MAXX(
ADDCOLUMNS(
  SUMMARIZE(
    'Sales',
    [Sales Person],
    [Quarter]
  ),
  "Q2D", [Quarters to Date]
), [Q2D]
)

[pacificnwp]

I'm not sure what's going on, but I'm getting numbers that are much lower than expected, rather than 10 quarters it's showing 3 on some and 2 on another.  I tried adding in year to the summarize function, but then I get 1 for each value.