Forum Discussion

POSPOS's avatar
POSPOS
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Create calculated column with measure conditions using DAX

Hi all,

I have a sample data as below

Sample file is attaced here.

I have created three measures, to highlight the employees who has only "FY 24"and only "FY 25" values.

Measure 1 :
FY 24 = CALCULATE(COUNT('Sample data'[Employee Name]),'Sample data'[FY]="FY24")
Measure 2:
FY 25 = CALCULATE(COUNT('Sample data'[Employee Name]),'Sample data'[FY]="FY25")
Measure 3:
Indicator 24_25 = 
[FY 24]-[FY 25]

Now, I am trying to create a caluclated column in such a way that
if a employee has both FY 24 and FY 25 then show as "Both",
if only FY24 then show as "dropped from FY24"
if only FY 25 then "added in FY25".

 

I tried to create a cal column using the Measure 3 I created, but does not give expected results.

Can someone please help in providing a solution on how to achieve this using DAX.

 

Thank you

  • Hi POSPOS 

    try this 

    FY_Status =
    VAR HasFY24 = CALCULATE(COUNTROWS('Sample data'), 'Sample data'[FY] = "FY24", ALLEXCEPT('Sample data', 'Sample data'[Employee Name])) > 0
    VAR HasFY25 = CALCULATE(COUNTROWS('Sample data'), 'Sample data'[FY] = "FY25", ALLEXCEPT('Sample data', 'Sample data'[Employee Name])) > 0

    RETURN
    SWITCH(
    TRUE(),
    HasFY24 && HasFY25, "Both",
    HasFY24, "Dropped from FY24",
    HasFY25, "Added in FY25",
    "Unknown"
    )

3 Replies

  • Hi POSPOS 

    try this 

    FY_Status =
    VAR HasFY24 = CALCULATE(COUNTROWS('Sample data'), 'Sample data'[FY] = "FY24", ALLEXCEPT('Sample data', 'Sample data'[Employee Name])) > 0
    VAR HasFY25 = CALCULATE(COUNTROWS('Sample data'), 'Sample data'[FY] = "FY25", ALLEXCEPT('Sample data', 'Sample data'[Employee Name])) > 0

    RETURN
    SWITCH(
    TRUE(),
    HasFY24 && HasFY25, "Both",
    HasFY24, "Dropped from FY24",
    HasFY25, "Added in FY25",
    "Unknown"
    )