Need DAX Help for calculated column

[Vijay08V]

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 Revised FTE, Locked FTE and Planned FTE values available (not zero), the logic should populate revised FTE value. Likewise if Revised FTE is zero then it should populate Locked FTE or else Planned FTE count. I tried using IF function and Switch function, still I'm not getting the expected output. Any help on this would be highly appreciated. Thanks!

Example 1 :

Tab Name	Table Name	Metrics	Date	Value	Vendor	Report Date	ID	Expected output
A	AB	Planned  FTE	12-31-2023	1553	ABC	26-05-24 0:00
A	AB	Hours	12-31-2023	37291	ABC	26-05-24 0:00
A	AB	Locked FTE	12-31-2023	892	ABC	26-05-24 0:00
A	AB	Revised FTE	12-31-2023	965	ABC	26-05-24 0:00		965
A	AB	Graduates	12-31-2023	39	ABC	26-05-24 0:00
A	AB	Week Four	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week Three	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week Two	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week One	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week One	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week Two	12-31-2023	0	ABC	26-05-24 0:00

Example 2 :

Tab Name	Table Name	Metrics	Date	Value	Vendor	Report Date	ID	Expected output
A	AB	Planned  FTE	12-31-2023	1553	ABC	26-05-24 0:00
A	AB	Hours	12-31-2023	37291	ABC	26-05-24 0:00
A	AB	Locked FTE	12-31-2023	892	ABC	26-05-24 0:00		892
A	AB	Revised FTE	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Graduates	12-31-2023	39	ABC	26-05-24 0:00
A	AB	Week Four	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week Three	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week Two	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week One	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week One	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week Two	12-31-2023	0	ABC	26-05-24 0:00

Example 3 :

Tab Name	Table Name	Metrics	Date	Value	Vendor	Report Date	ID	Expected output
A	AB	Planned  FTE	12-31-2023	1553	ABC	26-05-24 0:00		1553
A	AB	Hours	12-31-2023	37291	ABC	26-05-24 0:00
A	AB	Locked FTE	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Revised FTE	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Graduates	12-31-2023	39	ABC	26-05-24 0:00
A	AB	Week Four	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week Three	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week Two	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week One	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week One	12-31-2023	0	ABC	26-05-24 0:00
A	AB	Week Two	12-31-2023	0	ABC	26-05-24 0:00

DAX Measure Used :

           FTE = VAR Locked_FTE = CALCULATE(SUM(Table[Value]),Table[Metrics]="Locked FTE",Table[ID]=EARLIER(Table[ID]))

                 VAR Revised_FTE = CALCULATE(SUM(Table[Value]),Table[Metrics]="Revised FTE",Table[ID]=EARLIER(Table[ID]))

                 VAR Planned_FTE = CALCULATE(SUM(Table[Value]),Table[Metrics]="Planned FTE",Table[ID]=EARLIER(Table[ID]))

                 RETURN(

                        SWITCH(TRUE(),Revised_FTE<>0,Revised_FTE,Revised_FTE=0,Locked_FTE,Locked_FTE=0,Planned_FTE))

Here ID is nothing but the Concatenation of Tab,Table name, Vendor and Date fields so that in the output I don't get a aggregated value for all dates

[Anonymous · Accepted solution]

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]

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]

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

[Vijay08V]

Hi @Anonymous  - Thanks for sharing the PBIX file. It looks like the logic doesn't work if I add new data to the existing table. In real scenario, it will have more records .Same metrics will be repeated for different tab and table names and for different Date,vendor and report date.

Case Condition 1 :
Revised FTE <> 0, Locked FTE <>0 and planned FTE <> 0 
Expected Output : Revised FTE number

Case Condition 2 :
Revised FTE = 0, Locked FTE <>0 and planned FTE <> 0 
Expected Output : Locked FTE number

Case Condition 3 :
Revised FTE = 0, Locked FTE = 0 and planned FTE <> 0 
Expected Output : Planned FTE number

Snapshot from PBIX after adding new data

Could you please help how the existing DAX logic can be revised?

[Vijay08V]

Sample Data

Tab Name	Table Name	Metrics	Date	Value	Vendor	Report Date	ID	Expected output
A	AB	Planned FTE	2023-12-31	1553	ABC	26-05-24 0:00
A	AB	Hours	2023-12-31	37291	ABC	26-05-24 0:00
A	AB	Locked FTE	2023-12-31	892	ABC	26-05-24 0:00		892
A	AB	Revised FTE	2023-12-31	0	ABC	26-05-24 0:00
A	AB	Graduates	2023-12-31	39	ABC	26-05-24 0:00
A	AB	Week Four	2023-12-31	0	ABC	26-05-24 0:00
A	AB	Week Three	2023-12-31	0	ABC	26-05-24 0:00
A	AB	Week Two	2023-12-31	0	ABC	26-05-24 0:00
A	AB	Week One	2023-12-31	0	ABC	26-05-24 0:00
A	AB	Week One	2023-12-31	0	ABC	26-05-24 0:00
A	AB	Week Two	2023-12-31	0	ABC	26-05-24 0:00
A	AB	Planned FTE	2024-01-07	1553	ABC	27-05-24 0:00
A	AB	Hours	2024-01-07	37291	ABC	27-05-24 0:00
A	AB	Locked FTE	2024-01-07	0	ABC	27-05-24 0:00
A	AB	Revised FTE	2024-01-07	500	ABC	27-05-24 0:00		500
A	AB	Graduates	2024-01-07	39	ABC	27-05-24 0:00
A	AB	Week Four	2024-01-07	0	ABC	27-05-24 0:00
A	AB	Week Three	2024-01-07	0	ABC	27-05-24 0:00
A	AB	Week Two	2024-01-07	0	ABC	27-05-24 0:00
A	AB	Week One	2024-01-07	0	ABC	27-05-24 0:00
A	AB	Week One	2024-01-07	0	ABC	27-05-24 0:00
A	AB	Week Two	2024-01-07	0	ABC	27-05-24 0:00
A	AB	Planned FTE	2024-01-14	1553	ABC	28-05-24 0:00		1553
A	AB	Hours	2024-01-14	37291	ABC	28-05-24 0:00
A	AB	Locked FTE	2024-01-14	0	ABC	28-05-24 0:00
A	AB	Revised FTE	2024-01-14	0	ABC	28-05-24 0:00
A	AB	Graduates	2024-01-14	39	ABC	28-05-24 0:00
A	AB	Week Four	2024-01-14	0	ABC	28-05-24 0:00
A	AB	Week Three	2024-01-14	0	ABC	28-05-24 0:00
A	AB	Week Two	2024-01-14	0	ABC	28-05-24 0:00
A	AB	Week One	2024-01-14	0	ABC	28-05-24 0:00
A	AB	Week One	2024-01-14	0	ABC	28-05-24 0:00
A	AB	Week Two	2024-01-14	0	ABC	28-05-24 0:00
A	AC	Planned FTE	2023-12-31	158	ADB	26-05-24 0:00
A	AC	Hours	2023-12-31	37291	ADB	26-05-24 0:00
A	AC	Locked FTE	2023-12-31	892	ADB	26-05-24 0:00
A	AC	Revised FTE	2023-12-31	10	ADB	26-05-24 0:00		10
A	AC	Graduates	2023-12-31	39	ADB	26-05-24 0:00
A	AC	Week Four	2023-12-31	0	ADB	26-05-24 0:00
A	AC	Week Three	2023-12-31	0	ADB	26-05-24 0:00
A	AC	Week Two	2023-12-31	0	ADB	26-05-24 0:00
A	AC	Week One	2023-12-31	0	ADB	26-05-24 0:00
A	AC	Week One	2023-12-31	0	ADB	26-05-24 0:00
A	AC	Week Two	2023-12-31	0	ADB	26-05-24 0:00
A	AC	Planned FTE	2024-01-07	1553	ADB	27-05-24 0:00
A	AC	Hours	2024-01-07	37291	ADB	27-05-24 0:00
A	AC	Locked FTE	2024-01-07	200	ADB	27-05-24 0:00		200
A	AC	Revised FTE	2024-01-07	0	ADB	27-05-24 0:00
A	AC	Graduates	2024-01-07	39	ADB	27-05-24 0:00
A	AC	Week Four	2024-01-07	0	ADB	27-05-24 0:00
A	AC	Week Three	2024-01-07	0	ADB	27-05-24 0:00
A	AC	Week Two	2024-01-07	0	ADB	27-05-24 0:00
A	AC	Week One	2024-01-07	0	ADB	27-05-24 0:00
A	AC	Week One	2024-01-07	0	ADB	27-05-24 0:00
A	AC	Week Two	2024-01-07	0	ADB	27-05-24 0:00
A	AC	Planned FTE	2024-01-14	1553	ADB	28-05-24 0:00		1553
A	AC	Hours	2024-01-14	37291	ADB	28-05-24 0:00
A	AC	Locked FTE	2024-01-14	0	ADB	28-05-24 0:00
A	AC	Revised FTE	2024-01-14	0	ADB	28-05-24 0:00
A	AC	Graduates	2024-01-14	39	ADB	28-05-24 0:00
A	AC	Week Four	2024-01-14	0	ADB	28-05-24 0:00
A	AC	Week Three	2024-01-14	0	ADB	28-05-24 0:00
A	AC	Week Two	2024-01-14	0	ADB	28-05-24 0:00
A	AC	Week One	2024-01-14	0	ADB	28-05-24 0:00
A	AC	Week One	2024-01-14	0	ADB	28-05-24 0:00
A	AC	Week Two	2024-01-14	0	ADB	28-05-24 0:00

[Vijay08V]

any help on this?