Forum Discussion
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])) > 0RETURN
SWITCH(
TRUE(),
HasFY24 && HasFY25, "Both",
HasFY24, "Dropped from FY24",
HasFY25, "Added in FY25",
"Unknown"
)
3 Replies
- POSPOS
Post Partisan
ArwaAldoud - Thank you for providing this solution. It worked!
- ArwaAldoud
Super User
You're welcome POSPOS Glad to hear that it worked.
- ArwaAldoud
Super User
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])) > 0RETURN
SWITCH(
TRUE(),
HasFY24 && HasFY25, "Both",
HasFY24, "Dropped from FY24",
HasFY25, "Added in FY25",
"Unknown"
)