Forum Discussion

Jensej's avatar
Jensej
Icon for Helper V rankHelper V
6 years ago
Solved

Checking Employee Count on specific date.

Hello There!    So im pretty new to Power BI.    Our company has some Groups with assisted living. (5 floors/groups)    I have made a report where i can see Total amount of residents,gender, av...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Jensej ,

    Just update your old measure, not write a new measure, add the group field in the below filter, refer to the previous formula in variable:

    filter(allexcept(bi_employee,bi_employee(gender),bi_employee(group)),...)

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
6 years ago

Hi Jensej ,

Continue to add the Group field in the filter which is similar with the previous formula:

var _employees = 
CALCULATE(
    COUNT('bi_employee'[id]),
    FILTER(
        ALLEXCEPT(bi_employee,bi_employee[Gender],bi_employee[Group]),
        'bi_employee'[Entry_Date] <= _date &&
        ('bi_employee'[Resign_Date] = BLANK() || 'bi_employee'[Resign_Date] > _date)
    )
)

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Jensej's avatar
Jensej
Icon for Helper V rankHelper V
6 years ago

I don't really understand what you mean, create a new Measure or update my old one?

 

I have 3 different measures atm because 2 visuals is with a Count (Pie Chart, Anzahl Bewohner) and 1 is a AVG (Durschnitt Alter) and 1 is SUM (BESA Punkte). 

 

For the Pie Chart and Anzahl Bewohner i have this one:

Anzahl Bewohner = 

Var _Date = MIN(bi_datum[datum])

Var _Bew = CALCULATE(COUNT('bi_bewohner'[AdressNrADR]),

FILTER ( ALLEXCEPT(bi_bewohner, bi_bewohner[Geschlecht]),'bi_bewohner'[Wohnen Eintritt] <= _Date &&

('bi_bewohner'[Wohnen Austritt] = BLANK() || 'bi_bewohner'[Wohnen Austritt] > _Date) ) ) 

RETURN IF ( _Date = BLANK(), "Bitte Datum auswählen", _Bew)

For the Durschnitt Alter

Durchschnitt Alter = 

Var _Date = MIN(bi_datum[datum])

Var _Bew = CALCULATE(AVERAGE('bi_bewohner'[Alter in Jahren]),

FILTER ( ALLEXCEPT(bi_bewohner, bi_bewohner[Alter in Jahren]),'bi_bewohner'[Wohnen Eintritt] <= _Date &&

('bi_bewohner'[Wohnen Austritt] = BLANK() || 'bi_bewohner'[Wohnen Austritt] > _Date) ) ) 

RETURN IF ( _Date = BLANK(), "Bitte Datum auswählen", _Bew)

For the BESA Punkte

BESA Punkte = 

Var _Date = MIN(bi_datum[datum])

Var _Bew = CALCULATE(SUM('bi_bewohner'[Punkte BESA]),

FILTER ( ALLEXCEPT(bi_bewohner, bi_bewohner[Punkte BESA]),'bi_bewohner'[Wohnen Eintritt] <= _Date &&

('bi_bewohner'[Wohnen Austritt] = BLANK() || 'bi_bewohner'[Wohnen Austritt] > _Date) ) ) 

RETURN IF ( _Date = BLANK(), "Bitte Datum auswählen", _Bew)

 

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Jensej ,

    Just update your old measure, not write a new measure, add the group field in the below filter, refer to the previous formula in variable:

    filter(allexcept(bi_employee,bi_employee(gender),bi_employee(group)),...)

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • Jensej's avatar
    Jensej
    Icon for Helper V rankHelper V
    6 years ago

    Hi v-yingjl 

     

    Perfekt now it works. I added this part on all 3 measures

    FILTER ( ALLEXCEPT(bi_bewohner, bi_bewohner[Geschlecht],bi_bewohner[Gruppe],bi_bewohner[Alter in Jahren],bi_bewohner[Punkte BESA]),

    Don't know if it necessary on all 3 measures but it works 🙂 Thanks