Summarize with filters form a linked table

[Andy-K]

Good Morning

I have created the following summary table and now need to filter the results based on the categories listed in the  WorkOrders[TypeId]

Filter values  = "REF_P1, REF_P2, REF_P3 and REF_P4

Thanks in advance for your assistance

Task Status Count =

SUMMARIZE(

    WorkOrderTasks,

    WorkOrderTasks[Task Status],

    "Category_In Progress", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "In Progress"), "Category_Pending", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "Pending"), "Category_On Hold", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "On Hold"), "Category_Awaiting parts from supplier", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "Awaiting parts from supplier"), "Category_Complete at Site-FGas Activity", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "Complete at Site-FGas Activity"), "Category_Complete at Site-Gas Safe_ID", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "Complete at Site-Gas Safe_ID"), "Category_Complete at Site-Materials Fitted", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "Complete at Site-Materials Fitted"), "Category_Complete at Site-Materials Required", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "Complete at Site-Materials Required"), "Category_Complete at Site-No Action Required", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "Complete at Site-No Action Required"), "Category_Complete at Site-Return Visit Required", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "Complete at Site-Return Visit Required"), "Category_TASK COMPLETE- Parts To Return", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "TASK COMPLETE- Parts To Return"), "Category_TASK COMPLETE- Review", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "TASK COMPLETE- Review"), "Category_TASK Complete-Credit Required", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "TASK Complete-Credit Required"), "Category_TASK Complete-Documentation Required", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "TASK Complete-Documentation Required"), "Category_TASK Complete-Financial Query", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "TASK Complete-Financial Query"), "Category_TASK COMPLETE-No Action Required", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "TASK Complete-No Action Required"), "Category_TASK Complete-Portal Upload", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "TASK Complete-Portal Upload"), "Category_TASK Complete-Technical Query", CALCULATE(COUNTROWS(WorkOrderTasks), WorkOrderTasks[Task Status] = "TASK Complete-Technical Query"))

[v-kpoloju-msft]

Hi @Andy-K,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @grazitti_sapna, for his inputs on this thread.

Has your issue been resolved? If the response provided by the community member @grazitti_sapna, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[Andy-K]

Thanks to @grazitti_sapna the problem i'd been fighting with for two day has been resolved

[grazitti_sapna · Accepted solution]

Hi @Andy-K,

Instead of repeating the filter each time, you could pre-filter your SUMMARIZE like this

Task Status Count =
SUMMARIZE (
FILTER (
WorkOrderTasks,
RELATED ( WorkOrders[TypeId] ) IN { "REF_P1", "REF_P2", "REF_P3", "REF_P4" }
),
WorkOrderTasks[Task Status],
"Category_In Progress", CALCULATE ( COUNTROWS ( WorkOrderTasks ), WorkOrderTasks[Task Status] = "In Progress" ),
"Category_Pending", CALCULATE ( COUNTROWS ( WorkOrderTasks ), WorkOrderTasks[Task Status] = "Pending" ),
"Category_On Hold", CALCULATE ( COUNTROWS ( WorkOrderTasks ), WorkOrderTasks[Task Status] = "On Hold" )
-- etc…
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!