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
Erik_Debono's avatar
Erik_Debono
Advocate II
6 years ago

Hi Anonymous Nathaniel_C 

 

Error was self inflicted, your solution worked great, thank you for that. Have marked as Solved. Are you able to explain what's happening here? i'm not following how it works. 

 

 

amitchandak  Thanks for your solution. I didn't get around to checking if it would work in this case. 

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.