Forum Discussion

pacificnwp's avatar
pacificnwp
Frequent Visitor
11 months ago
Solved

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

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 ...
Need Help
  • MasonMA's avatar
    MasonMA
    11 months ago

    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's avatar
pacificnwp
Frequent Visitor
11 months ago

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's avatar
MasonMA
Icon for Super User rankSuper User
11 months ago

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's avatar
    pacificnwp
    Frequent Visitor
    11 months ago

    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's avatar
      MasonMA
      Icon for Super User rankSuper User
      11 months ago

      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's avatar
        pacificnwp
        Frequent Visitor
        11 months ago

        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's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      11 months ago

      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.