Forum Discussion
selecting specific value from a table (with condition)
Hello experts,
I'm tring to select a specific value from a table in order to use it in other calculations - numerical type.
The value i'm looking for is remaining_working_days = 9.5 (rec_month = 8, rec_year=2021), but as i want to make it a dynamic query i don't want to specipy it by specific month and year (as 8,2021).
This table connected to my dim_date table as well.
This is the query i created but it returns blank value.
test1111 =
var rec_month = SELECTEDVALUE(DIM_BO_WDAYS[REC_MONTH])
var month_from_dim_date = SELECTEDVALUE(Dim_Dates[Month])
var current_year = SELECTEDVALUE(Dim_Dates[Year])
var rec_year = SELECTEDVALUE(DIM_BO_WDAYS[REC_YEAR])
var filter_year = IF(rec_year=current_year,rec_year)
var remaining_WDay_month = IF(rec_month=month_from_dim_date && filter_year,SELECTEDVALUE(DIM_BO_WDAYS[Remaining_Working_Days]))
return remaining_WDay_month
Anyone knows how to fix this query or to solve it in other way?
Thanks in advance!
Hi!
Actually it wasn't the issue in the end i solved it by the following query:
Remaining_Working_Days =var rec_month = SELECTEDVALUE(DIM_BO_WDAYS[REC_MONTH])var month_from_dim_date = SELECTEDVALUE(Dim_Dates[Month])var current_year = SELECTEDVALUE(Dim_Dates[Year])var rec_year = SELECTEDVALUE(DIM_BO_WDAYS[REC_YEAR])var filter_year = IF(rec_year=current_year,rec_year)var clc = CALCULATE(SELECTEDVALUE(DIM_BO_WDAYS[Remaining_Working_Days]),FILTER(ALL(DIM_BO_WDAYS[REC_MONTH], DIM_BO_WDAYS[REC_YEAR]), DIM_BO_WDAYS[REC_YEAR]=current_year && DIM_BO_WDAYS[REC_MONTH]=month_from_dim_date))return clcBut still thanks for responding:)
2 Replies
Your IF statements are missing the ELSE part.
Hi!
Actually it wasn't the issue in the end i solved it by the following query:
Remaining_Working_Days =var rec_month = SELECTEDVALUE(DIM_BO_WDAYS[REC_MONTH])var month_from_dim_date = SELECTEDVALUE(Dim_Dates[Month])var current_year = SELECTEDVALUE(Dim_Dates[Year])var rec_year = SELECTEDVALUE(DIM_BO_WDAYS[REC_YEAR])var filter_year = IF(rec_year=current_year,rec_year)var clc = CALCULATE(SELECTEDVALUE(DIM_BO_WDAYS[Remaining_Working_Days]),FILTER(ALL(DIM_BO_WDAYS[REC_MONTH], DIM_BO_WDAYS[REC_YEAR]), DIM_BO_WDAYS[REC_YEAR]=current_year && DIM_BO_WDAYS[REC_MONTH]=month_from_dim_date))return clcBut still thanks for responding:)
