how to use filter with summarize function

[sandip]

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
group by
'Task SLA'[task], 'Task SLA'[has_breached], 'Task SLA'[sla]

and have made a similar kind of DAX like below:

Business1 =

SUMMARIZE(

'Task SLA',

'Task SLA'[task],

'Task SLA'[has_breached],

'Task SLA'[sla],

"Business Duration", SUM('Task SLA'[Business Duration Days])

)

and there is no relationship between them.

So my intention is filter the task column in the above table where

task_sla.task = child.number

and task_sla table has many records based on 'Task' filed, so I have used group by claue to get the

business duration value. So any suggestion to use filter condition?

below is the link of the data file:

 https://drive.google.com/file/d/17wU7ZF21g02pKlH2qb0Pc1NXzc2XzxFh/view?usp=sharing

[amitchandak]

@sandip , You can use

filter(Crossjoin(Table1,table2),table1[a]=table2[a])

but this is very costly

refer these are few options here

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

also like

summarize(filter(Table1,Table1[a] = max(Table2[a])),Table1[id],Table1[a],"Measure1",[measure]....)

[sandip]

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]

Hi @sandip ,

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.

[sandip]

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]

Hi @sandip ,

"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.

[sandip]

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

[v-lionel-msft]

Hi @sandip ,

You can remove duplicate rows of 'child' table and 'parent' table in 'Edit Query', then create one to many relationship between tables.

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.