Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
@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]....)
Hi,
I have used the below DAX:
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.
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?
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |