Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sivarajan21
Post Patron
Post Patron

Calculated column to find the header of corresponding row value

Hi,

 

I have below sample dataset:

PoinTS_idData dateMonthProfileDirectTarget UnitAccrual kwh
12301/04/2022 00:00Apr  4444
12302/04/2022 00:00Apr3 443
12303/04/2022 00:00Apr  4444
12304/04/2022 00:00Apr  4444
12305/04/2022 00:00Apr  4444
12306/04/2022 00:00Apr  4444
12307/04/2022 00:00Apr  4444
12308/04/2022 00:00Apr50 4450
12309/04/2022 00:00Apr65 4465
12310/04/2022 00:00Apr0 440
12311/04/2022 00:00Apr87 4487
12612/04/2022 00:00Apr 633.333333336
12613/04/2022 00:00Apr  33.3333333333.33333333
12614/04/2022 00:00Apr  33.3333333333.33333333
12615/04/2022 00:00Apr 833.333333338

 

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_idData dateMonthProfileDirectTargetAccrual kwhAccrual Source
12301/04/2022 00:00Apr  4444Target
12302/04/2022 00:00Apr3 443Profile
12303/04/2022 00:00Apr  4444Target
12304/04/2022 00:00Apr  4444Target
12305/04/2022 00:00Apr  4444Target
12306/04/2022 00:00Apr  4444Target
12307/04/2022 00:00Apr  4444Target
12308/04/2022 00:00Apr50 4450Profile
12309/04/2022 00:00Apr65 4465Profile
12310/04/2022 00:00Apr0 440Profile
12311/04/2022 00:00Apr87 4487Profile
12612/04/2022 00:00Apr 633.333333336Direct
12613/04/2022 00:00Apr  33.3333333333.33333333Target
12614/04/2022 00:00Apr  33.3333333333.33333333Target
12615/04/2022 00:00Apr 833.333333338Direct

 

Could you please guide me through this?

 

Thanks in advance

@Ahmedx @VijayP @Ashish_Mathur @lbendlin @grantsamborn 

1 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"
)

 

View solution in original post

6 REPLIES 6
Kishore_KVN
Super User
Super User

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
Super User
Super User

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

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_idData dateMonthProfileDirectTargetAccrual kwhAccrual Source
12301/04/2022 00:00Apr44444444Profile

 

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

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

 

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

 

Hi @lbendlin ,

 

Thanks for your quick response and beautiful code!

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

 

Many thanks

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors