topic Re: Using Countrows based on multiple conditions in DAX Commands and Tips

Using Countrows based on multiple conditions

Hyuna_8000 — Thu, 12 Sep 2024 05:26:30 GMT

Hi,

I have a dataset with the following following columns, basically I just wanted to have a calculated column, that populates the number of actions when the Action is "Promotion" or "Secondment" or both for each employee. And for employee with no Separation Date, I want the count of the number of Promotion or Secondment or both when the Effective date is between today and 12 months ago. For employee with a Separation Date, the count should be when the Effective date equals or is greater than 12 months before the Separation Date and is less than the Separation Date.

Please see this dataset, the "No. of Promotion and Secondment last 12 months" is the calculated column I want:

Row	Employee Name	Effective Date	Action	Separation Date	No. of Promotion and Secondment last 12 months
1	Jane Jones	8/02/2025	Hire		0
2	Carrie Bradshaw	1/01/2024	Secondment		2
3	Carrie Bradshaw	6/06/2024	Promotion		2
4	Carrie Bradshaw	26/10/2021	Secondment		2
5	Rebecca Lynn	16/10/2022	Hire		1
6	Rebecca Lynn	14/10/2023	Secondment		1
7	Yao Ming	7/10/2022	Secondment	7/06/2023	1
8	Zoe Meng	7/01/2024	Add Worker		0
9	Hoi Lou	5/10/2021	Hire	2/02/2023	1
10	Hoi Lou	5/11/2021	Secondment	2/02/2023	1
11	Hoi Lou	6/06/2022	Promotion	2/02/2023	1

I did try this Dax code for the calculated column, but it only returns either 1 for row with Promotion or Secondment with the effective date and separation date condition. For example, for Carrie Bradshaw, it only returns 1 for the Row 2 and 3, and 0 for Row 4, but what I want is 2 for Row 2, 3 and 4.

No. of Promotion and Secondment last 12 months =
VAR CurrentEmployee = 'Data'[Employee Name]
VAR SeparationDate = 'Data'[Separation Date]
VAR EffectiveStartDate = 'Data'[Effective Date]
VAR Last12MonthsStartDate = TODAY() - 365
RETURN
IF(
ISBLANK(SeparationDate),
CALCULATE(
COUNTROWS('Data'),
'Data'[Employee Name] = CurrentEmployee,
('Data'[Action] = "Promotion" && 'Employment Action History'[Action] = "Secondment"),
'Data'[Effective Start Date] >= Last12MonthsStartDate
),
CALCULATE(
COUNTROWS('Data'),
'Data'[Employee Name] = CurrentEmployee,
('Data'[Action] = "Promotion" && 'Data'[Action] = "Secondment"),
'Data'[Effective Date] >= SeparationDate - 365,
'Data'[Effective Date] <= SeparationDate
)
)

Thanks in advance, much appreciated!

Re: Using Countrows based on multiple conditions

bhanu_gautam — Thu, 12 Sep 2024 06:27:28 GMT

Hyuna_8000 , Try below DAX mentioned:-

No. of Promotion and Secondment last 12 months =
VAR CurrentEmployee = 'Data'[Employee Name]
VAR SeparationDate = 'Data'[Separation Date]
VAR EffectiveStartDate = 'Data'[Effective Date]
VAR Last12MonthsStartDate = TODAY() - 365

RETURN
IF(
ISBLANK(SeparationDate),
CALCULATE(
COUNTROWS('Data'),
'Data'[Employee Name] = CurrentEmployee,
'Data'[Action] IN {"Promotion", "Secondment"},
'Data'[Effective Date] >= Last12MonthsStartDate
),
CALCULATE(
COUNTROWS('Data'),
'Data'[Employee Name] = CurrentEmployee,
'Data'[Action] IN {"Promotion", "Secondment"},
'Data'[Effective Date] >= SeparationDate - 365,
'Data'[Effective Date] < SeparationDate
)
)

Re: Using Countrows based on multiple conditions

Hyuna_8000 — Thu, 12 Sep 2024 06:36:14 GMT

Hi bhanu_gautam
Thanks for your prompt reply, really appreciated it!
It still returns 1 for individual row that matches the criteria, instead of summing up or count the total rows that match the criteria. I wonder if I should use Sumx instead, so far no luck yet.

Re: Using Countrows based on multiple conditions

bhanu_gautam — Thu, 12 Sep 2024 06:38:06 GMT

Hyuna_8000 , Yes you can try using SUMX

No. of Promotion and Secondment last 12 months =
VAR CurrentEmployee = 'Data'[Employee Name]
VAR SeparationDate = 'Data'[Separation Date]
VAR Last12MonthsStartDate = TODAY() - 365

RETURN
IF(
ISBLANK(SeparationDate),
CALCULATE(
SUMX(
FILTER(
'Data',
'Data'[Employee Name] = CurrentEmployee &&
'Data'[Action] IN {"Promotion", "Secondment"} &&
'Data'[Effective Date] >= Last12MonthsStartDate
),
1
)
),
CALCULATE(
SUMX(
FILTER(
'Data',
'Data'[Employee Name] = CurrentEmployee &&
'Data'[Action] IN {"Promotion", "Secondment"} &&
'Data'[Effective Date] >= SeparationDate - 365 &&
'Data'[Effective Date] < SeparationDate
),
1
)
)
)