topic Re: dax, value if measure on other table on same level data,store > 0 in DAX Commands and Tips

dax, value if measure on other table on same level data,store > 0

AntonL — Fri, 10 Mar 2023 20:01:13 GMT

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

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

Re: dax, value if measure on other table on same level data,store > 0

tamerj1 — Sat, 11 Mar 2023 13:47:32 GMT

Hi AntonL

please try

Re: dax, value if measure on other table on same level data,store > 0

AntonL — Sat, 11 Mar 2023 18:30:42 GMT

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)

Re: dax, value if measure on other table on same level data,store > 0

tamerj1 — Sun, 12 Mar 2023 18:33:33 GMT

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 ) )

AntonL

Re: dax, value if measure on other table on same level data,store > 0

AntonL — Mon, 13 Mar 2023 19:13:13 GMT

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

Re: dax, value if measure on other table on same level data,store > 0

tamerj1 — Mon, 13 Mar 2023 19:21:37 GMT

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

AntonL