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
Icon for Community Support rankCommunity 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
      Icon for Community Support rankCommunity 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi,

        I have used this idea , but havae one problem with realted functionaluty as it only support one to many relation ship while in my case I have many to many relation ship with task table to child and parent. So what should I do in that case.

         

        Thanks,

        Sandip