locked
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. RRS feed

  • Question

  • The following measure is close to what I want to do. It sums rows based on their value in the EmploymentLevel and accumulate over time.

    FullTimeEquivalentEmployeesStarted:=CALCULATE(SUM([EmploymentLevel]);FILTER(ALL(tDates[Date]);tDates[Date]<=MAX(tDates[Date])))


    In addition to what the measure above does I also want rows in tEmployees to be excluded if they have a blank value in [DateStarted]:

    I have tried the following measure, but it doesn't work as intended. When I add the second filter, the one on tEmployees, the measure for some 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 am writing this measure in the calculation area beneath tEmployees.

    Can you help me figure out how this measure should be written to work as I want it to work?

    FullTimeEquivalentEmployeesStarted:=?


    Friday, July 31, 2015 6:30 AM

Answers

  • Is there a relationship between tEmployees[DateStarted] and tDates[Date]. If yes, mark that as inactive or delete it, so it doesn't interfere with the relationship to your fact table.

    What table is [EmploymentLevel] in and what are its relations to tDates and tEmployees?

    Is tEmployees[DateStarted] actually a DateTime field, or is it text? If text, are the values actually BLANK() or are they whitespace strings? In DAX, "" = BLANK() returns True, but " " = BLANK() returns False (unlike SQL where non-significant whitespace is equivalent to an empty string).

    If, instead, your measure is defined on your tEmployees dimension with no other fact table, rewrite your measure to the following:
    FullTimeEquivalentEmployeesStarted2:=
    CALCULATE(
        SUM([EmploymentLevel])
        ;FILTER(
            ALL(tDates[Date])
            ;tDates[Date]<=MAX(tDates[Date])
        )
        ;NOT(ISBLANK(tEmployees[DateStarted]))
    )



    GNet Group BI Consultant

    Thursday, August 6, 2015 12:50 PM

All replies

  • Can you just filter out DateStarted = BLANK in your visualization?
    Friday, July 31, 2015 10:27 AM
  • I might be able to apply that filter in the visualization. But I much prefer if it is possible to apply that filter in the measure.
    Friday, July 31, 2015 10:58 AM
  • Is there a relationship between tEmployees[DateStarted] and tDates[Date]. If yes, mark that as inactive or delete it, so it doesn't interfere with the relationship to your fact table.

    What table is [EmploymentLevel] in and what are its relations to tDates and tEmployees?

    Is tEmployees[DateStarted] actually a DateTime field, or is it text? If text, are the values actually BLANK() or are they whitespace strings? In DAX, "" = BLANK() returns True, but " " = BLANK() returns False (unlike SQL where non-significant whitespace is equivalent to an empty string).

    If, instead, your measure is defined on your tEmployees dimension with no other fact table, rewrite your measure to the following:
    FullTimeEquivalentEmployeesStarted2:=
    CALCULATE(
        SUM([EmploymentLevel])
        ;FILTER(
            ALL(tDates[Date])
            ;tDates[Date]<=MAX(tDates[Date])
        )
        ;NOT(ISBLANK(tEmployees[DateStarted]))
    )



    GNet Group BI Consultant

    Thursday, August 6, 2015 12:50 PM