Forum Discussion

Hyuna_8000's avatar
Hyuna_8000
Helper I
1 year ago
Solved

Using Countrows based on multiple conditions

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"...
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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
    )
    )
    )

Hyuna_8000's avatar
Hyuna_8000
Helper I
1 year ago

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.

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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
)
)
)