Forum Discussion
How do I create a global max value that references another measure?
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 ExpandedCalc 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.
Super UserThis 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.
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
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 ExpandedCalc 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.
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).
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?
- 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.
