DAX: Create complex measure with multiple aggregations over multiple table columns

Hi Smopure,

Thanks for taking time to review my message. Let me try briefing you again on the logic that I want to implement

Problem statement:

When, End user selects a time frame from the reporting layer, we need to calculate a measure called 'Regular Counts' for the active organizations within the time frame selected.

Explanation:

In example 1 above, the organization is not active in the time frame selected. So we will not report the regular count for that organization (or) we can simply report it as 0.

In example 2 above, the organizaion is active and we need to calculate the regular count for that organization. The calculation logic is as provided in the above message. I will try explaining that in a more simpler way.

Notes:

1. An organization can have multiple groups, and each group will have a payroll run which tracks the counts of the organization.

2. When I say regular count, we are considering the records with count type as 1

3. When I say special count, we are considering the records with count type as 2

Steps to calculate:

1. In step 1 above, we are getting all the records of the groups which have a payroll run in the selected time frame, and then getting the MAXIMUM of regular count for each group with in the time frame. (We have payroll runs only for groups G1 and G2 in the selected time frame. Also, note that for G2 we are considering only the record that is within the time frame selected)

2. In the step 2, we are identifying the groups of that organization that did not have a payroll run in the selected time frame (Here it is G3), and then we are getting the regular count value from the previous run (which is on 12/31/2016). Here the regular count for the run on 12/31/2016 is 5

3. In the step 3, we will calculate the special count values for each of the groups.

• Calculating special count values for each of the groups in the selected time frames is same as that of calculating regular count values as explained in steps 1 and 2. (Except that the records with count type 2 are considered

4. In the step 4, we will we check if the special count value for that group in the time period is 0. If it is 0, then we will ignore that group's count in the final aggregate.

5. Now, we will do a SUM of counts for each valid group to get the organization count.

Please let me know if this is possible in a DAX measure. If you can't still follow the logic, let me know the step at which you are unable to follow the logic.

Thanks