Forum Discussion

Vijay08V's avatar
Vijay08V
Icon for Helper III rankHelper III
2 years ago
Solved

Need DAX Help for calculated column

Hi,   I have a similar table as below and I'm trying to introduce a calculated column which evaluates at row level and gives me the FTE count. The logic that I'm trying to put is when I have Revise...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  Vijay08V ,

    You can try the following dax.

    Expected output123 =
var _column=
SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Tab Name ]=EARLIER('Table'[Tab Name ])&&'Table'[Table Name]=EARLIER('Table'[Table Name])&&'Table'[Date]=EARLIER('Table'[Date])&&'Table'[Value]=0),"test",[Metrics])
RETURN
SWITCH(
    TRUE(),
      NOT("Revised FTE") in _column && NOT("Locked FTE") in _column && NOT("Planned FTE")  in _column&&'Table'[Metrics]="Revised FTE",[Value],
      "Revised FTE" in _column && NOT("Locked FTE") in _column && NOT("Planned FTE")  in _column&&'Table'[Metrics]="Locked FTE",[Value],
      "Revised FTE" in _column && "Locked FTE" in _column && NOT("Planned FTE")  in _column&&'Table'[Metrics]="Planned FTE",[Value],
      NOT("Revised FTE") in _column && "Locked FTE" in _column && NOT("Planned FTE")  in _column&&'Table'[Metrics]="Revised FTE",[Value])

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi  Vijay08V ,

 

Here are the steps you can follow：

1. Create calculated column.

Expected output =
var _count=
COUNTX(
    FILTER(ALL('Table'),
    'Table'[Metrics] in {"Revised FTE","Locked FTE","Planned FTE"} && 'Table'[Value]<>0),[Value])
return
SWITCH(
    TRUE(),
    _count=3&&'Table'[Metrics] = "Revised FTE",
    SUMX(FILTER('Table','Table'[Metrics] = "Revised FTE"),[Value]),
    _count=2&&'Table'[Metrics] = "Locked FTE",
    SUMX(FILTER('Table','Table'[Metrics] = "Locked FTE"),[Value]),
    _count=1&&'Table'[Metrics] = "Planned FTE",
    SUMX(FILTER('Table','Table'[Metrics] = "Planned FTE"),[Value]))

2. Result:

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Vijay08V's avatar
Vijay08V
Icon for Helper III rankHelper III
2 years ago

Hi Anonymous  

I made few changes to the DAX measure by introducing ID column in the table and its working but there is one challenge though. If value of  _count variable is two or one, it directly fetches the value of Locked FTE or Planned FTE. However the logic should flow in the hireachy of Revised FTE >> Locked FTE>>Planned FTE. Say for example value of _count variable is two and we have Revised FTE and Planned FTE metrics as nonzero then the logic should pick Revised FTE value and not the locked FTE.  Any Idea how to resolve this problem? Thanks!

Expected Output = 

var _count=

COUNTX(

    FILTER(ALL('Table'), 'Table'[ID]= EARLIER('Table'[ID]) &&

    'Table'[Metrics] in {"Revised FTE","Locked FTE","Planned FTE"} && 'Table'[Value]<>0),[Value])

RETURN

SWITCH(

    TRUE(),

    _count=3&&'Table'[Metrics] = "Revised FTE ,

    SUMX(FILTER('Table','Table'[Metrics] = "Revised FTE" && 'Table'[ID]=EARLIER('Table'[ID])),[Value]),

    _count=2&&'Table'[Metrics] = "Locked FTE",

    SUMX(FILTER('Table','Table'[Metrics] = "Locked FTE"&& 'Table'[ID]=EARLIER('Table'[ID])),[Value]),

    _count=1&&'Table'[Metrics] = "Planned FTE",

    SUMX(FILTER('Table','Table'[Metrics] = "Planned FTE" && 'Table'[ID]=EARLIER('Table'[ID])),[Value]))