Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

how to use filter with summarize function

Hi, I want to create dax expressionon below sql: select sum(business_duration), 'Task SLA'[task], 'Task SLA'[has_breached], 'Task SLA'[sla] from task_sla where task_sla.task = child.number grou...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi,

I have used the below DAX:

Child Task SLA2 =
SUMMARIZE(
filter('Task SLA', 'Parent'[number] = Max('Task SLA'[task])),
'Task SLA'[task],
'Task SLA'[has_breached],
'Task SLA'[sla],
'Task SLA'[Child Task],
'Task SLA'[Duration Bucket],
"Business Duration", SUM('Task SLA'[Business Duration Days])
)
but I am facing the below error message:
A table of multiple values was supplied where a single value was expected.
any help on this?
 
  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Maybe you should create relationship first.

    Then Create a calculated table.

    Table = 
    SUMMARIZE(
         FILTER( 'Task Sla', 'Task Sla'[task] = RELATED(Child[number]) ),
        'Task SLA'[task],
        'Task SLA'[has_breached],
        'Task SLA'[sla],
        "Business Duration", SUM('Task Sla'[Business])
    )

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-lionel-msft,

      I have 3 tables in my data model : Parent , Child and Task_SLA table. Both Parent and Child table is realted to TASK_SLA table on

      'task' column and need to get the sum of business duration in days from task sla table.

      So as per your suggestion I have to make realtion of task_sla table with both Parent and Child tables and then need to create a calculated table.

      But should it be pobbible to connect 2 table with one table on the same field simulteniouly ? Please advise what should I do now?

      • v-lionel-msft's avatar
        v-lionel-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        "But should it be pobbible to connect 2 table with one table on the same field simulteniouly ? "

        Yes, you can.

         

        Maybe you can try this formula.

        Table = 
    SUMMARIZE(
         FILTER( 'Task Sla', 'Task Sla'[task] = RELATED(Child[number]) && 'Task Sla'[task] = RELATED(Parent[number]) ),
        'Task SLA'[task],
        'Task SLA'[has_breached],
        'Task SLA'[sla],
        "Business Duration", SUM('Task Sla'[Business])
    )

         

        Best regards,
        Lionel Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.