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

[AntonL]

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

[tamerj1]

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

[AntonL]

@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

[tamerj1]

 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

[tamerj1]

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

[AntonL]

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)