Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there. This is my problem - there is a table with project names, tasks and time (for simplicity there is only 1 project). Each project may have different tasks, and each tasks will take different time to complete. There is also an entry "total" which has to be the sum of time of all tasks for every single project. Project and tasks are entries from one table, and time from another - in that another table there is no entry "total", only tasks, and i can't add it, that's why i need to sum it here somehow, which i can't do, that why i'll be glad if you'll help me. I guess that to complete it we need to create another column (or measure), with condition that if task = "total" then we need to sum all time for each project, and if not we just need to take time without changing.
In the end it should be like this:
Solved! Go to Solution.
Hi @Yonko ,
Based on your description, I have creeated a simple sample:
Please try:
Time =
VAR _a =
SUM ( 'Table (2)'[Time] )
VAR _b =
SELECTCOLUMNS (
FILTER ( ALL ( 'Table' ), [Project] = MAX ( 'Table'[Project] ) ),
"Task", [Task]
)
VAR _c =
SUMX ( FILTER ( ALL ( 'Table (2)' ), [Task] IN _b ), [Time] )
RETURN
IF ( MAX ( 'Table'[Task] ) = "Total", _c, _a )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Yonko ,
Based on your description, I have creeated a simple sample:
Please try:
Time =
VAR _a =
SUM ( 'Table (2)'[Time] )
VAR _b =
SELECTCOLUMNS (
FILTER ( ALL ( 'Table' ), [Project] = MAX ( 'Table'[Project] ) ),
"Task", [Task]
)
VAR _c =
SUMX ( FILTER ( ALL ( 'Table (2)' ), [Task] IN _b ), [Time] )
RETURN
IF ( MAX ( 'Table'[Task] ) = "Total", _c, _a )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Yonko
please try
Time Measure =
VAR CurrentTask =
SELECTEDVALUE ( Table1[Task] )
VAR NormalValue =
SUM ( Table2[Time] )
VAR TotalValue =
CALCULATE ( SUM ( Table2[Time] ), ALL ( Table1[Task] ) )
RETURN
IF ( CurrentTask = "Total", TotalValue, NormalValue )
@tamerj1 The problem in your formula happens with TotalValue = it should return sum of values (8), but instead it returns values without changing (3 and 5). If it somehow will return 8 it will work, and i tried different approaches but not succeeded. Any suggestions?
@Anonymous It also returns the same values, not sum of them. I guess it happens because it filtered not only by projects, but also by tasks, so it's granularity moves from project to task and it's not working. I don't know how to fix it sadly 😞
Maybe you can share an example file ?