Calculated column to find the header of corresponding row value

[sivarajan21]

Hi,

I have below sample dataset:

PoinTS_id	Data date	Month	Profile	Direct	Target Unit	Accrual kwh
123	01/04/2022 00:00	Apr			44	44
123	02/04/2022 00:00	Apr	3		44	3
123	03/04/2022 00:00	Apr			44	44
123	04/04/2022 00:00	Apr			44	44
123	05/04/2022 00:00	Apr			44	44
123	06/04/2022 00:00	Apr			44	44
123	07/04/2022 00:00	Apr			44	44
123	08/04/2022 00:00	Apr	50		44	50
123	09/04/2022 00:00	Apr	65		44	65
123	10/04/2022 00:00	Apr	0		44	0
123	11/04/2022 00:00	Apr	87		44	87
126	12/04/2022 00:00	Apr		6	33.33333333	6
126	13/04/2022 00:00	Apr			33.33333333	33.33333333
126	14/04/2022 00:00	Apr			33.33333333	33.33333333
126	15/04/2022 00:00	Apr		8	33.33333333	8

I need to create a calculated column(Source) to find where the 'Accrual kwh' value is derived from. for example, if we look at the first row,

01/04/2022 has  'Accrual kwh' of 44 derived from the Target Unit. so the Source column(new) should have Target Unit for that row.

similarly for 2nd row, 02/04/2022 has  'Accrual kwh' of 3 which is derived from Profile so the Source column will have Profile in it.

Sample file in below location:

Missing Calendar.pbix

My expected output would be:

PoinTS_id	Data date	Month	Profile	Direct	Target	Accrual kwh	Accrual Source
123	01/04/2022 00:00	Apr			44	44	Target
123	02/04/2022 00:00	Apr	3		44	3	Profile
123	03/04/2022 00:00	Apr			44	44	Target
123	04/04/2022 00:00	Apr			44	44	Target
123	05/04/2022 00:00	Apr			44	44	Target
123	06/04/2022 00:00	Apr			44	44	Target
123	07/04/2022 00:00	Apr			44	44	Target
123	08/04/2022 00:00	Apr	50		44	50	Profile
123	09/04/2022 00:00	Apr	65		44	65	Profile
123	10/04/2022 00:00	Apr	0		44	0	Profile
123	11/04/2022 00:00	Apr	87		44	87	Profile
126	12/04/2022 00:00	Apr		6	33.33333333	6	Direct
126	13/04/2022 00:00	Apr			33.33333333	33.33333333	Target
126	14/04/2022 00:00	Apr			33.33333333	33.33333333	Target
126	15/04/2022 00:00	Apr		8	33.33333333	8	Direct

Could you please guide me through this?

Thanks in advance

@Ahmedx @VijayP @Ashish_Mathur @lbendlin @grantsamborn 

[Kishore_KVN]

Accrual Source = If(MonthProfileDirect = blank()&&Target = Accrual kwh,"Target",
                 If(MonthProfileDirect <> blank()&&MonthProfieDirect = Accrual kwh,"Profile",
                 If(MonthProfileDirect <> blank()&& Accrual kwh = blank(),"Direct",
                 If(MonthProfileDirect = blank()&& Accrual kwh = blank(),"Target",
                 blank()))))

Use this DAX with proper column names.

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

[lbendlin]

what should happen if you have ambiguous data. Let's say in the first row Profile, Direct and Target all say 44 ?

[sivarajan21]

Hi @lbendlin ,

Thanks for your quick response!

That's a valid question and appreciate for asking it.

Order of preference is first given to Profile, if all three have values, if profile is blank give Direct, if Direct is blank then give Target.

In that case, we will choose the Profile  and Accrual Source column should have Profile as below:

PoinTS_id	Data date	Month	Profile	Direct	Target	Accrual kwh	Accrual Source
123	01/04/2022 00:00	Apr	44	44	44	44	Profile

The ultimate goal of creating this column is to use this in coalesce function as below:

Accrual kwh =
COALESCE (
'Missing Calendar date_1'[Profile],
'Missing Calendar date_1'[Direct],
'Missing Calendar date_1'[Target]
)

Please let me know if you need further info

Thanks in advance

[lbendlin · Accepted solution]

COALESCE is perfect for the value but not that suitable for the source text. You would have to go with SWITCH

Accrual Source =
SWITCH ( TRUE(),
NOT ISBLANK([Profile]),"Profile",
NOT ISBLANK([Direct]),"Direct",
NOT ISBLANK([Target]),"Target"
)

[sivarajan21]

Hi @lbendlin ,

Many thanks for this amazing solution!😊

This works like a charm and I will accept this as a solution & close this query

Kind regards

[sivarajan21]

Hi @lbendlin ,

Thanks for your quick response and beautiful code!

I will do a test and get back to you on this

Many thanks