how to use filter with summarize function

Question

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_slawheretask_sla.task = child.numbergroup 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 wheretask_sla.task = child.number&nbsp;and task_sla table has many records based on 'Task' filed, so I have used group by claue to get thebusiness duration value. So any suggestion to use filter condition?&nbsp;below is the link of the data file:&nbsp;https://drive.google.com/file/d/17wU7ZF21g02pKlH2qb0Pc1NXzc2XzxFh/view?usp=sharing&nbsp;&nbsp;&nbsp;&nbsp;

amitchandak · Answer

Anonymous , 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/
&nbsp;
also like
summarize(filter(Table1,Table1[a] = max(Table2[a])),Table1[id],Table1[a],"Measure1",[measure]....)

anonymous · Answer

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?&nbsp;

v-lionel-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
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])
    )

&nbsp;
Best regards,Lionel Chen
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

anonymous · Answer

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 · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
"But should it be pobbible to connect 2 table with one table on the same field simulteniouly ?&nbsp;"
Yes, you can.
&nbsp;
Maybe you can try this formula.
Table = 
    SUMMARIZE(
         FILTER( 'Task Sla', 'Task Sla'[task] = RELATED(Child[number]) &amp;&amp; 'Task Sla'[task] = RELATED(Parent[number]) ),
        'Task SLA'[task],
        'Task SLA'[has_breached],
        'Task SLA'[sla],
        "Business Duration", SUM('Task Sla'[Business])
    )
&nbsp;
Best regards,Lionel Chen
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

how to use filter with summarize function

7 Replies

Related content

Summarize error but no Summarize function

Caclulate function over riding filter?

Parameter in summarize

filter function

SUMMARIZE FUNCTION in measures