Forum Discussion

Erik_Debono's avatar
Erik_Debono
Icon for Advocate II rankAdvocate 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
    Icon for Advocate II rankAdvocate 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
      Icon for Community Champion rankCommunity 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

       

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

        Anonymous perfect. I understand now. Thank you.