Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hi all
need help
have a table with employees movement documents(hired, dismissed)
with DateID, StoreID columns
# hired:=
CALCULATE(sum(fact_Movement[action_value]),FILTER(fact_Movement,[action_type_code]=1))
and then running total on date level
# hired rt:=
VAR MaxDate = MAX (dim_Date[DateID]) -- Saves the last visible date
RETURN
CALCULATE ([# hired],dim_Date[DateID] <= MaxDate,ALL(dim_Date))
the same for dissmissed as for hired(but for dissmissed documents [action_type_code]=2)
# dissmissed := ....same schema as hired
# dissmissed rt: = ....same schema as hired
-- its number of employes on any date
# employees:= calculate ([# hired rt]+[# dismissed rt])
so i have as result date, store, # employees
next:
also ihave SCD table for staffing count( table show that for this store in period from .... to .... planned count of employees = xx)
StoreID, date_from, date_to, staff_count
with this formula i'm calculating number of planned staff on any date level
# staffing:=
VAR max_date = MAX (dim_Date[DateID]) -- Saves the last visible date
return
CALCULATE ( sum(fact_Staffing_schedule_scd[staffing_count]),
fact_Staffing_schedule_scd[date_fr] <= max_date,
fact_Staffing_schedule_scd[date_to] > max_date
)
question:
i need to calculate [# staffing]
only if [# employees] on same DateID, StoreID > 0
help please)
i wrote this
# staffing 2:=
VAR max_date = MAX(dim_Date[DateID])
VAR max_store = MAX(dim_Stores[StoreID])
VAR employees_count = [# employees]
VAR stf_calc =
CALCULATE ( sum(fact_Staffing_schedule_scd[staffing_count]),
fact_Staffing_schedule_scd[date_fr] <= max_date,
fact_Staffing_schedule_scd[date_to] > max_date
)
VAR filter_table =
CALCULATETABLE(
VALUES(fact_Staffing_schedule_scd[store_id]),
fact_Staffing_schedule_scd[store_id] = max_store,
fact_Staffing_schedule_scd[date_fr] <= max_date,
fact_Staffing_schedule_scd[date_to] > max_date
)
RETURN
IF(employees_count > 0,
CALCULATE(stf_calc, filter_table),
BLANK()
)
so on date or store level it show OK
but on total, or subtotal level - it shows full value with excluded stores
Hi
I can understand from some of your amendments that dimStores and fact_Staffing_schedule_scd are not related therefore there is a need to add fact_Staffing_schedule_scd[store_id] = max_store filter inside CALCULATE but I don't understand the CALCULATETABLE part. What are yuou trying to acomplish?
The numbers will be greater than expected at aggregated levels you are right about that. Actually the sum aggregation has no meaning. Therefore, please try with max aggregation
# staffing 2 :=
MAXX (
CROSSJOIN ( VALUES ( dim_Date[DateID] ), VALUES ( dim_Stores[StoreID] ) ),
VAR max_date = dim_Date[DateID]
VAR max_store = dim_Stores[StoreID]
VAR employees_count = [# employees]
VAR stf_calc =
CALCULATE (
SUM ( fact_Staffing_schedule_scd[staffing_count] ),
fact_Staffing_schedule_scd[store_id] = max_store,
fact_Staffing_schedule_scd[date_fr] <= max_date,
fact_Staffing_schedule_scd[date_to] > max_date
)
RETURN
IF ( employees_count > 0, stf_calc )
)
@tamerj1 , thank you again
i rewrite you code a bit and almost received estimated result
# staffing 2:=
MAXX (
CROSSJOIN ( VALUES ( dim_Date[DateID] ), VALUES ( dim_Stores[StoreID] ) ),
VAR max_date = dim_Date[DateID]
VAR max_store = dim_Stores[StoreID]
VAR employees_count = [# employees]
VAR stf_calc =
CALCULATE (
SUM ( fact_Staffing_schedule_scd[staffing_count] ),
fact_Staffing_schedule_scd[store_id] = max_store,
fact_Staffing_schedule_scd[date_fr] <= max_date,
fact_Staffing_schedule_scd[date_to] > max_date
)
VAR stf_calc_r = IF (employees_count > 0, stf_calc )
RETURN
--max_date
CALCULATE(stf_calc_r, dim_Date[DateID]=max_date)
)
and now main issue in that it on group level, for example month it shows value only for one store
due to
fact_Staffing_schedule_scd[store_id] = max_store,
can you please help to rewrite it to 'selected stores', not only one max/last
A variable is calculated only once. You cannot recalculate a variable inside CALCULATE. The expression:
CALCULATE(stf_calc_r, dim_Date[DateID]=max_date)
)
does absolutely nothing
Hi @AntonL
please try
# staffing 2 :=
SUMX (
CROSSJOIN ( VALUES ( dim_Date[DateID] ), VALUES ( dim_Stores[StoreID] ) ),
VAR max_date = dim_Date[DateID]
VAR max_store = dim_Stores[StoreID]
VAR employees_count = [# employees]
VAR stf_calc =
CALCULATE (
SUM ( fact_Staffing_schedule_scd[staffing_count] ),
fact_Staffing_schedule_scd[date_fr] <= max_date,
fact_Staffing_schedule_scd[date_to] > max_date
)
RETURN
IF ( employees_count > 0, stf_calc, filter_table )
)
thank you @tamerj1 for your reply
i also added to your query
VAR filter_table =
CALCULATETABLE(
VALUES(fact_Staffing_schedule_scd[store_id]),
fact_Staffing_schedule_scd[store_id] = max_store,
fact_Staffing_schedule_scd[date_fr] <= max_date,
fact_Staffing_schedule_scd[date_to] > max_date
)
full code of measure is:
# staffing 2:=
SUMX (
CROSSJOIN ( VALUES ( dim_Date[DateID] ), VALUES ( dim_Stores[StoreID] ) ),
VAR max_date = dim_Date[DateID]
VAR max_store = dim_Stores[StoreID]
VAR employees_count = [# employees]
VAR stf_calc =
CALCULATE (
SUM ( fact_Staffing_schedule_scd[staffing_count] ),
fact_Staffing_schedule_scd[store_id] = max_store,
fact_Staffing_schedule_scd[date_fr] <= max_date,
fact_Staffing_schedule_scd[date_to] > max_date
)
VAR filter_table =
CALCULATETABLE(
VALUES(fact_Staffing_schedule_scd[store_id]),
fact_Staffing_schedule_scd[store_id] = max_store,
fact_Staffing_schedule_scd[date_fr] <= max_date,
fact_Staffing_schedule_scd[date_to] > max_date
)
RETURN
IF ( employees_count > 0, stf_calc, filter_table )
)
but its calculate some very big numbers...(not real)
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |