subquery in power bi dax

[Anonymous]

How do i write sub query in dax?

Query : 
select YEAR([CloseDate]),MONTH([CloseDate]), sum(days_to_close)/count(distinct id) as 'Number of Days to Close' from
(select [CloseDate], id, days_to_close from [dbo].[test]
where sname='Closed' group by [CloseDate], id, days_to_close) a
group by YEAR([CloseDate]),MONTH([CloseDate])
order by 1,2

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

Sample data:

You need to add year ,month on base table:

Then use the below dax to create a new table:

TEST2 = SUMMARIZE(FILTER(test,test[sname]="Closed"),test[YEAR],test[MONTH],"Number of Days to Close",DIVIDE(SUM(test[days_to_close]),DISTINCTCOUNT(test[id])))

Output result:

And in sql:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien