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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
David_1970
Frequent Visitor

Help with calculated column

I have a table with the 3 first columns and want to calculate the fourth column. Which DAX-function?

 

ClientParent ClientClient ResponsibleParent Client Responsible
11AC
21AC
31CC
44DD
55EE
68AF
78CF
88FF
910GH
1010HH
1 ACCEPTED SOLUTION

Oops , mistake in copying the formula... corrected... you can check now

 

Formula.PNG

View solution in original post

4 REPLIES 4
afzalphatan
Resolver I
Resolver I

hi @David_1970

 

Hope the below formula does ur job

 

=
Var ParClientNo = Table1[Parent Client]
Var RefNo = CALCULATE(MAX(Table1[Client]), FILTER(Table1, Table1[Parent Client] = ParClientNo))
Return
CALCULATE(DISTINCT(Table1[Client Responsible]), FILTER(Table1, Table1[Client] = RefNo && Table1[Parent Client] = ParClientNo))

 

thanks

First, thanks for helping me, I am a newbie when it comes to DAX but I try to learn. This Var stuff is completely new to me.

 

Hmm, I didn't make your formula work correctly for all client numbers in my table.

 

I modified the below line (and was given more accurate answers) even though I'm not sure what I actually did.

 

Var RefNo = CALCULATE(MAX(Table1[Client]), FILTER(Table1, Table1[Parent Client] = ParClientNo), Table1[Client])

 

First I removed the last argument in the CALCULATE function above, i.e. Table1[Client] because I'm not sure how it actually would do any filtering job.

 

Second, I changed Table1[Parent Client] = ParClientNo so it instead reads  Table1[Client] = ParClientNo

 

 

Oops , mistake in copying the formula... corrected... you can check now

 

Formula.PNG

Your formula is perfectly correct. It was my initial table that was wrong. Very sorry for that. It should have read like this instead, i.e. Parent Client Responsible for Parent Client 1 should be A and not C, see below. Thus, the client that is the parent client determines Parent Client Responsible.

 

ClientParent ClientClient ResponsibleParent Client Responsible
11AA
21AA
31CA
44DD
55EE
68AF
78CF
88FF
99GG
109HG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.