Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I'm very new to DAX and now I'm having some trouble with a calculation I need to make. My question is how to get the amount of people that is present on a certain day (or month), based on the begin and end date and for which organisation group they are present. Due to confidential information I cant give you guys a data set, but I made an example which I hope will help my explanation.
This is an example of the data I have. So I need to determine how many persons are present in a certain organisation group, while they can also be active in multiple groups at the same time. The end date shows when they left a certain group.
This next example made in Excel is what should be the outcome (I need to know the amount present after a certain day). This information should be presented based on a certain group in the organization.
Solved! Go to Solution.
-- Say, you have a parameter table disconnected -- from the model that stores all the days -- you're interested in. Let's name it DaysOfInterest. -- So, this table has only one column: Date. -- Let's name the table you've posted Presence. -- This measure gives the number of people -- where StartDate >= [day of interest] and -- ( EndDate <= [day of interest] or EndDate is BLANK ). -- You should hide StartDate and EndDate and never -- slice by them. For dates use the dates in -- DaysOfInterest. [# Present] = var __oneDayOfInterestSelected = HASONEFILTER( DaysOfInterest[Date] ) var __dayOfInterest = VALUES( DaysOfInterest[Date] ) var __peoplePresent = CALCULATE( DISTINCTCOUNT( Presence[Person_ID] ), Presence[StartDate] >= __dayOfInterest, __dayOfInterest <= Presence[EndDate] || ISBLANK( Presence[EndDate] ) ) return if( __oneDayOfInterestSelected, __peoplePresent ) -- The above measure will work OK whether -- or not you're slicing by Group_ID. If -- you don't, then it'll calculate the number -- of people present on the selected day -- regardless of which group they belong to. -- In other words, if there is at least one -- group a person belongs to on the selected day, -- the person will be counted. If you filter -- by group, then it'll show the the number -- of people that belong to the group on the -- selected day. By the way, you should change -- the model to ease the calculation. Instead -- of leaving blank in the EndDate, you should -- put something like 9999-01-01 in there, so -- that the || ISBLANK( Presence[EndDate] ) -- part can be removed. This will speed up -- the calculation.
Best
Darek
Well, this is not clear enough. The Excel table does not show how Organization_ID affects the calculation.
Best
Darek
I should have called it Group_id. As you can see in the second excel it is the amount of people present at a certain group.
So what I eventually need is the amount of people that are present at a day, month, year for a certain group. Based on the amount of people that are coming in or are leaving (for that group).
Best,
Lars
-- Say, you have a parameter table disconnected -- from the model that stores all the days -- you're interested in. Let's name it DaysOfInterest. -- So, this table has only one column: Date. -- Let's name the table you've posted Presence. -- This measure gives the number of people -- where StartDate >= [day of interest] and -- ( EndDate <= [day of interest] or EndDate is BLANK ). -- You should hide StartDate and EndDate and never -- slice by them. For dates use the dates in -- DaysOfInterest. [# Present] = var __oneDayOfInterestSelected = HASONEFILTER( DaysOfInterest[Date] ) var __dayOfInterest = VALUES( DaysOfInterest[Date] ) var __peoplePresent = CALCULATE( DISTINCTCOUNT( Presence[Person_ID] ), Presence[StartDate] >= __dayOfInterest, __dayOfInterest <= Presence[EndDate] || ISBLANK( Presence[EndDate] ) ) return if( __oneDayOfInterestSelected, __peoplePresent ) -- The above measure will work OK whether -- or not you're slicing by Group_ID. If -- you don't, then it'll calculate the number -- of people present on the selected day -- regardless of which group they belong to. -- In other words, if there is at least one -- group a person belongs to on the selected day, -- the person will be counted. If you filter -- by group, then it'll show the the number -- of people that belong to the group on the -- selected day. By the way, you should change -- the model to ease the calculation. Instead -- of leaving blank in the EndDate, you should -- put something like 9999-01-01 in there, so -- that the || ISBLANK( Presence[EndDate] ) -- part can be removed. This will speed up -- the calculation.
Best
Darek
Thanks for your answer, but the only problem now is that I get a (blank) value when I select a group for which I want to see the amount of people that are present.
Best,
Lars
Please paste the structure of the model in here.
Best
Darek
There was a problem with my model. It didn't have a relation with the organisation table. It works fine for me now. Thanks for your help!
Best,
Lars
User | Count |
---|---|
57 | |
21 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |