Forum Discussion

Jensej's avatar
Jensej
Helper 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.

Jensej's avatar
Jensej
Helper V
6 years ago

It works now, it was a problem with the Datamodeling. 

 

I can now choose a date in the Slicer and my measure shows me the Total Residents at that day. 

 

Now to the next problem 😞 

 

As showned in the Picture i want to Add a Pie Chart that shows me the Gender from the 46 Residents at that day. 

If i put Geschlecht (Gender in German) on the Legend and the Measure Anzahl Bew (Total Residents) as Values i get 46 everyone. It already summed up. I want to see that it was 20 Females and 26 Males at that day. How can i solve this? 

 

 

 

If i put the for example the Employee ID in Values instead of my new Measures it splits after Gender but all the Amount of Residents are showned and not the 46 at that specific date. 

v-yingjl's avatar
v-yingjl
Community Support
6 years ago

Hi Jensej ,

You can modify your variable _empoyees like this:

var _employees = 
CALCULATE(
    COUNT('bi_employee'[id]),
    FILTER(
        ALLEXCEPT(bi_employee,bi_employee[Gender]),
        '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
    Helper V
    6 years ago

    v-yingjl Thanks, your my hero! 

     

    Im now able to choose one Specific Date and all the Calculations are correct. 

     

    I have a second slicer on my Report where i should be able to filter on the different groups or combine some of them. 

    For example i should be able to select Gruppe A and Gruppe B and the numbers will change. After i made all the changes with the specific date it doesn't work anymore. Do i have to make some measure from the Gruppe field and use this instead of the Original in the slicer to make it work? 

     

  • v-yingjl's avatar
    v-yingjl
    Community 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
    Helper 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
    Community 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
    Helper 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