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

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.

Need DAX Help for calculated column123.pbix76 KB

Vijay08V

Helper III

2 years ago

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

Helper III

2 years ago

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

Forum Discussion

Need DAX Help for calculated column

Related content

TMDL - calculated column

Direct Lake Model - Calculated Column/Tables

IF calculated column

Power Query or Calculated columns

Dynamic Variable in Calculated Columns