Need help with a power pivot measure that should sum a column for each row where another column is non-empty, and should accumulate this sum over time.

the following measure is close want do. sums rows based on value in employmentlevel and accumulate over time.

fulltimeequivalentemployeesstarted:=calculate(sum([employmentlevel]);filter(all(tdates[date]);tdates[date]<=max(tdates[date])))

in addition measure above want rows in temployees excluded if have blank value in [datestarted]:

i have tried following measure, doesn't work intended. when i add second filter, 1 on temployees, measure reason no longer accumulates over time.

fulltimeequivalentemployeesstarted:=calculate(sum([employmentlevel]);filter(all(tdates[date]);tdates[date​m]<=max(tdates[date]));filter(temployees ;(temployees [datestarted])<>blank()))".

i writing measure in calculation area beneath temployees.

can me figure out how measure should written work want work?

fulltimeequivalentemployeesstarted:=?

is there relationship between temployees[datestarted] , tdates[date]. if yes, mark inactive or delete it, doesn't interfere relationship fact table.

table [employmentlevel] in , relations tdates , temployees?

temployees[datestarted] datetime field, or text? if text, values blank() or whitespace strings? in dax, "" = blank() returns true, " " = blank() returns false (unlike sql non-significant whitespace equivalent empty string).

if, instead, measure defined on temployees dimension no other fact table, rewrite measure following:

fulltimeequivalentemployeesstarted2:= calculate(     sum([employmentlevel])     ;filter(         all(tdates[date])         ;tdates[date]<=max(tdates[date])     )     ;not(isblank(temployees[datestarted])) )

---

gnet group bi consultant

SQL Server  >  Power Pivot