Forum Discussion
DAX Query - How to filter a table for a specific string in DAX Studio - SSAS
I am trying to import a table that contains data for the years 2020 and 2021. The planning week column is a String column and has the yyyy-ww format.
The DAX Query I been working on:
EVALUATE
CALCULATETABLE(
SUMMARIZE (
WorkOrderLabourS,
ROLLUP(WorkOrderLabourS[Hours Actual],WorkOrderLabourS[Hours Allocated],WorkOrderLabourS[Planning Week])
),
FILTER (WorkOrderLabourS, CONTAINSSTRING( WorkOrderLabourS[Planning Week], "2020" || "2021"))
)
However, with this query, I am getting the following error in DAX Studio.
Note: I am pulling the data from SSAS
Hi Anonymous
Try this:
SUMMARIZE ( FILTER (WorkOrderLabourS, left(WorkOrderLabourS[Planning Week],4)="2020" ||left(WorkOrderLabourS[Planning Week],4)="2021" ), ROLLUP(WorkOrderLabourS[Hours Actual],WorkOrderLabourS[Hours Allocated],WorkOrderLabourS[Planning Week]) )Or
SUMMARIZE ( FILTER (WorkOrderLabourS, CONTAINSSTRING ( WorkOrderLabourS[Planning Week], "2020" ) || CONTAINSSTRING ( WorkOrderLabourS[Planning Week], "2021" ) ), ROLLUP(WorkOrderLabourS[Hours Actual],WorkOrderLabourS[Hours Allocated],WorkOrderLabourS[Planning Week]) )If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
2 Replies
Hi Anonymous
Try this:
SUMMARIZE ( FILTER (WorkOrderLabourS, left(WorkOrderLabourS[Planning Week],4)="2020" ||left(WorkOrderLabourS[Planning Week],4)="2021" ), ROLLUP(WorkOrderLabourS[Hours Actual],WorkOrderLabourS[Hours Allocated],WorkOrderLabourS[Planning Week]) )Or
SUMMARIZE ( FILTER (WorkOrderLabourS, CONTAINSSTRING ( WorkOrderLabourS[Planning Week], "2020" ) || CONTAINSSTRING ( WorkOrderLabourS[Planning Week], "2021" ) ), ROLLUP(WorkOrderLabourS[Hours Actual],WorkOrderLabourS[Hours Allocated],WorkOrderLabourS[Planning Week]) )If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/I'm pretty sure || won't work like that inside FILTER.
Try this instead:
FILTER ( WorkOrderLabourS, CONTAINSSTRING ( WorkOrderLabourS[Planning Week], "2020" ) || CONTAINSSTRING ( WorkOrderLabourS[Planning Week], "2021" ) )
