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

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 

 

Thank you for that. I tried your formula and i'm getting underline errors for the following:

  1. Name 2 [ID] on line 5
  2. Name 3 [CycleTime] on line 6 and
  3. Expression [CycleTime] on the last line.

The overall error is "Function SELECTCOLUMNS expects a column name as argument number 4"

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Erik_Debono , Anonymous ,
    I built a table using copy and paste from your numbers, and it works for me.
    Nathaniel