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

[raviteja1994]

Hi All.. I have the following tables in my data model with the below given sample data

DimOrganizationStatus:

OrgID	IsActive	StartDate	EndDate
1	1	01-01-2000	01-01-2010
1	0	01-01-2010	12-01-2016
1	1	12-01-2016	02-01-2017
1	0	02-01-2017	9999-12-3

FactCount

OrgID	GroupID	Date	Count	CountType
1	G1	01/01/2017	10	1
1	G1	01/15/2017	15	1
1	G2	01/20/2017	20	1
1	G2	12/31/2016	25	1
1	G3	12/31/2016	5	1
1	G1	01/01/2017	0	2
1	G1	01/15/2017	2	2
1	G2	01/20/2017	0	2
1	G2	12/31/2016	1	2
1	G3	12/31/2016	1	2

Points to Note:

1. Count Type ‘1’ suggest that the count signifies regular count
2. Count Type ‘2’ suggest that the count signifies special count
3. Each organization will have many groups. Each group will have 2 different types of counts for each date (on which the counts are taken) record

Consider the following examples of the time period selected by the user in the repor:

Eg1: User selects the time frame of 02-01-2017 -  02-28-2017

Output: We will not output any data for that organization as it is not active in the selected time frame

Eg2: User selects the time frame 01-01-2017 - 01-31-2017.

The organization is active and we need to display the regular count values of that organization during the reporting period.

Output result calculation steps: (We have total of 1 org and 3 groups)

1. Calculate Max(Regular Count) (count type = 1)  for each of the Group that have a date in the selected time frame. Output would be

1	G1	15
1	G2	20

1. Calculate Count for each of the Group that did not have a date in the selected time frame based on the latest date prior to selected time frame. Output would be:

1

G3

5

In case of G3, since it did not have a date in selected time frame and the date available in the table prior to it is 12/31/2016, we get the count value for count type 1 which is 5

1. Now for each Group, we need to calculate the special count values (the calculation logic is similar to regular count with in the selected time frame except that the count typechanges to 2). If special count value is 0 for that group, then we will ignore that group’s count while calculating the regular count. After implementing this, special count values for each group would then be. (In some cases, special count values can be null instead of 0. If it is null then we need to consider the regular count record)

1	G1	2
1	G2	0 (will be Ignored as special count is 0)
1	G3	1

1. Since Special Count value for G2 is 0, remove G2 from the above list. The final organization and group counts would be

1	G1	15
1	G3	5

1. Now, sum up all Group counts to get regular count for that organization during the reporting period which is 20.

Can we implement this regular count as a measure in DAX? Could someone please help me in creating that measure?

Thanks in advance.

[Greg_Deckler]

I'm not following the logic here.

[raviteja1994]

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