Forum Discussion

cheid_4838's avatar
cheid_4838
Icon for Helper IV rankHelper IV
1 year ago
Solved

How to show single value with interaction between visuals and max value?

I have two tables (Components and mechanicid) that show cost, total hours, averagehours, and max labor hours.  My goal with these two tables is for the user to select the max labor hour from the component table and the record(s) for that value show up in the mechanicID table.  Right now when I select the max labor hoursfor the first row all the mechanics show up that worked on that component.  Is there anyway to change the logic so that only RHARNER shows up?  Below is the logic I am using for max value.

 

Max Labor Hours = MAXX('Labor Hours','Labor Hours'[Hours])

 

 

Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    Hi cheid_4838 ,

    Thanks for posting your question in Microsoft Fabric Community.

    I reproduced the scenario using the sample structure you described. 

    The approach shared earlier by Sandip_Palit using the Show Mechanic for Max Hours measure works correctly when evaluated in the row context of the mechanics visual. I followed that logic but slightly adjusted the setup to handle both cases: when a component is selected and when nothing is selected (so all rows show by default).

     

    To achieve the behavior where selecting a row in the Components table filters the Mechanic table to show only the record with the matching max labor hours, you can use the following measure:

    SelectedComponentMaxHours = 
CALCULATE (
    MAX (ComponentLabor[Max Labor Hours]),
    ALLEXCEPT (ComponentLabor, ComponentLabor[ComponentDesc])
)

     

    Then use this filter measure on your mechanic visual:

    ShowOnlyMaxMechanic = 
VAR _SelectedMax =
    CALCULATE (
        MAX (ComponentLabor[Max Labor Hours]),
        ALLEXCEPT (ComponentLabor, ComponentLabor[ComponentDesc])
    )

VAR _IsFiltered =
    ISFILTERED (ComponentLabor[ComponentDesc])

RETURN
    IF (
        NOT _IsFiltered || MAX(MechanicLabor[Max Labor Hours]) = _SelectedMax,
        1,
        0
    )

     

    Apply ShowOnlyMaxMechanic = 1 as a visual-level filter on the mechanic table visual. This ensures that when a component is selected, only the mechanic(s) with matching max labor hours will show, and when no selection is made, all rows will be visible.

     

    Result:
    Without selection:

     

    When selected:

     

    Attached is the .pbix for reference.

    Thanks Sandip_Palit for your suggestions and approach.

     

    Hope this helps. Please reach out for further assistance.

    Thank you.

3 Replies

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi cheid_4838 ,

    Thanks for posting your question in Microsoft Fabric Community.

    I reproduced the scenario using the sample structure you described. 

    The approach shared earlier by Sandip_Palit using the Show Mechanic for Max Hours measure works correctly when evaluated in the row context of the mechanics visual. I followed that logic but slightly adjusted the setup to handle both cases: when a component is selected and when nothing is selected (so all rows show by default).

     

    To achieve the behavior where selecting a row in the Components table filters the Mechanic table to show only the record with the matching max labor hours, you can use the following measure:

    SelectedComponentMaxHours = 
CALCULATE (
    MAX (ComponentLabor[Max Labor Hours]),
    ALLEXCEPT (ComponentLabor, ComponentLabor[ComponentDesc])
)

     

    Then use this filter measure on your mechanic visual:

    ShowOnlyMaxMechanic = 
VAR _SelectedMax =
    CALCULATE (
        MAX (ComponentLabor[Max Labor Hours]),
        ALLEXCEPT (ComponentLabor, ComponentLabor[ComponentDesc])
    )

VAR _IsFiltered =
    ISFILTERED (ComponentLabor[ComponentDesc])

RETURN
    IF (
        NOT _IsFiltered || MAX(MechanicLabor[Max Labor Hours]) = _SelectedMax,
        1,
        0
    )

     

    Apply ShowOnlyMaxMechanic = 1 as a visual-level filter on the mechanic table visual. This ensures that when a component is selected, only the mechanic(s) with matching max labor hours will show, and when no selection is made, all rows will be visible.

     

    Result:
    Without selection:

     

    When selected:

     

    Attached is the .pbix for reference.

    Thanks Sandip_Palit for your suggestions and approach.

     

    Hope this helps. Please reach out for further assistance.

    Thank you.

  • Sandip_Palit's avatar
    Sandip_Palit
    Icon for Super User rankSuper User
    1 year ago

    This approach will give you the exact interaction you're looking for. Please follow it step by step.

     

    Step 1: Create an Efficient Max Hours Measure
    First, let's ensure your base measure is as efficient as possible. Using MAX is more direct than MAXX for finding the maximum value in a single column.

    Create or replace your existing measure with this simpler DAX formula:

    Max Labor Hours = MAX('Labor Hours'[Hours])

     

    Step 2: Create the Filter Flag Measure
    Next, create this new DAX measure. This measure will dynamically identify which mechanic(s) worked the maximum hours for the selected component.

    Show Mechanic for Max Hours =
    -- Calculate the max hours for the selected component using our efficient base measure
    VAR MaxHoursInContext = [Max Labor Hours]

    -- Get the hours for the mechanic in the current row of the table
    VAR CurrentMechanicHours = SUM('Labor Hours'[Hours])

    -- Return 1 if the mechanic's hours match the max, otherwise return 0
    RETURN
    IF(CurrentMechanicHours = MaxHoursInContext, 1, 0)

     

    Note: This measure works because when it's evaluated in the context of the Mechanics table, the [Max Labor Hours] part of the calculation is evaluated across all mechanics for the selected component, while [CurrentMechanicHours] is evaluated just for the mechanic in that specific row.

     

    Step 3: Apply the Filter to Your Mechanics Table
    Select your second table visual (the one showing EMPID).

    In the Filters pane, drag your new measure, [Show Mechanic for Max Hours], into the Filters on this visual section.

    Set the filtering condition to is 1.

    Click Apply filter.

     

    Now, when you click the "Gauges & Warning Devices" row, the [Show Mechanic for Max Hours] measure will correctly identify that only RHARNER's hours for that job (7.0) match the Max Labor Hours for that component (7.0), and the table will be filtered accordingly.

     

    If this explanation and solution resolve your issue, please like and accept the solution.

    • cheid_4838's avatar
      cheid_4838
      Icon for Helper IV rankHelper IV
      1 year ago

      Something doesn't seem right.  When I write this logic it's not showing the mechanic that has 6.97 hours.

      MaxHours =
      VAR MaxHoursInContext =
          MAX('Labor Hours'[Hours])
      VAR CurrentMechanicHours =
          SUM('Labor Hours'[Hours])
      RETURN
      IF(CurrentMechanicHours = MaxHoursInContext,1,0)
       

       

       

      COuld you do something similar by looking up MAXX value in a column with ALLEXCEPT and add 1 or 0 to a column?