Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table with the 3 first columns and want to calculate the fourth column. Which DAX-function?
Client | Parent Client | Client Responsible | Parent Client Responsible |
1 | 1 | A | C |
2 | 1 | A | C |
3 | 1 | C | C |
4 | 4 | D | D |
5 | 5 | E | E |
6 | 8 | A | F |
7 | 8 | C | F |
8 | 8 | F | F |
9 | 10 | G | H |
10 | 10 | H | H |
Solved! Go to Solution.
Oops , mistake in copying the formula... corrected... you can check now
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
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.
Client | Parent Client | Client Responsible | Parent Client Responsible |
1 | 1 | A | A |
2 | 1 | A | A |
3 | 1 | C | A |
4 | 4 | D | D |
5 | 5 | E | E |
6 | 8 | A | F |
7 | 8 | C | F |
8 | 8 | F | F |
9 | 9 | G | G |
10 | 9 | H | G |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |