Forum Discussion

Erik_Debono's avatar
Erik_Debono
Advocate II
6 years ago
Solved

Dax Average with Distinct Filter

Hi All,   I'm trying to filter a table (have provided a mock one below) where I filter by Distinct and a result. As you can see, the Cycle time will be the same for every ID but I only want to Aver...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Erik_Debono -

    Try the following:

    Cycle Time Average = 
var _table = DISTINCT(
    SELECTCOLUMNS(
        FILTER('Cycle Time', [Result]<>"N/A"), 
        "ID", [ID],
        "CycleTime", [CycleTime]
    )
)
return AVERAGEX(_table, [CycleTime])

     

    I hope this helps. If it does, please Mark as a solution.
    I also appreciate Kudos.
    Nathan Peterson
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Erik_Debono  - 

Sure, it is basically 2 steps:

  1. Create a table of the distinct values:
    1. FILTER limits table to eliminate N/A
    2. SELECTCOLUMNS chooses the 2 columns we need to run the distinct on.
    3. DISTINCT returns the distinct values
      var _table = DISTINCT(
    SELECTCOLUMNS(
        FILTER('Cycle Time', [Result]<>"N/A"), 
        "ID", [ID],
        "CycleTime", [CycleTime]
    )
)
  2. Get the average of the CycleTimes from the distinct table.
    1. Use AVERAGEX to work with Table Variables.
return AVERAGEX(_table, [CycleTime])

 

Erik_Debono's avatar
Erik_Debono
Advocate II
6 years ago

Anonymous perfect. I understand now. Thank you.