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.

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

Hi Jensej ,

Try like this measure and put it in a card visual:

 

Measure =
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _people =
    CALCULATE (
        SUM ( 'Table'[count] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] <= _date )
    )
RETURN
    IF (
        _date = BLANK (),
        "Please select a date in the slicer",
        "The number of people as of the selcected date is: " & _people
    )

 

 

Attached a sample file that hopes to help you: Checking Employee Count on specific date.pbix

 

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 and amitchandak  

 

Thanks for your replies

 

So i think im pretty close to a solution now. 

 

I read in another artictle that the easiest way to have only one date to fill in is to only activate the after option in the slicer. Like this i don't have a long list with dates. In my measure i therefor have to change SELECTEDVALUE to MIN.  

 

I have this Measure but i still don't get the correct Value. 

 

I think the problem is at the Var _Employees...  I want to say where " Entry_Date is before or same Date as the VAR _Date and the Resign_Date is Empty OR after the VAR _Date. 

 

Is it correct that && = AND and ||= OR in Dax?

 

Employees at specific Date =

Var _Date = MIN(bi_datum[datum])

Var _Employees= CALCULATE(COUNT(bi_Employee[id]),

FILTER ( ALL ( bi_Employee), bi_Employee[Entry_Date] <= _Date &&

(bi_Employee[Resign_Date] = BLANK() || bi_Employee[Resign_Date] > _Date) ) ) 

RETURN IF ( _Date = BLANK(), "Please choose a Date", "Count of Employees at selected date is: " & _Employees

 

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

    Hi Jensej ,

    There seems to be no problem only from your formula. In dax, && = AND and || = OR are corrent so the logic has no problem.

    Perhaps need more information about this for further discussion.

     

    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.