Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Does anyone know how to add a column to a table by taking a value from another column depending on whether records in another column have the same id?
Exmaple table:
ID | Type | Interval |
123 | A | 1 |
234 | B | 2 |
345 | C | 3 |
345 | D | 4 |
768 | C | 3 |
987 | D | 4 |
So if in column type the value is C then lookup in column ID if there is a record with the same ID, if so take the interval value of D, otherwise use the value in the column interval.
So the result am looking for is
ID | Type | Interval | Result |
123 | A | 1 | 1 |
234 | B | 2 | 2 |
345 | C | 3 | 4 |
345 | D | 4 | 4 |
768 | C | 3 | 3 |
987 | D | 4 | 4 |
in this example ID 345 has for both records the value of type D.
Hopefully someone knows how to write this in a DAX calculated column.
thanks in advanced
Solved! Go to Solution.
@icturion Please try this:-
Result =
VAR _typeD =
CALCULATE (
MAX ( 'Table (3)'[Interval] ),
FILTER (
ALL ( 'Table (3)' ),
'Table (3)'[ID] = EARLIER ( 'Table (3)'[ID] )
&& 'Table (3)'[Type] = "D"
)
)
RETURN
IF (
'Table (3)'[Type] = "C",
IF ( NOT ( ISBLANK ( _typeD ) ), _typeD, 'Table (3)'[Interval] ),
'Table (3)'[Interval]
)
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @icturion ,
You can create a column with below codE:-
Result =
CALCULATE (
MAX ( 'Table (2)'[Interval] ),
FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[ID] = EARLIER ( 'Table (2)'[ID] ) )
)
Output:-
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @Samarth_18 ,
Thank you for the quick respons.
It's just not quite the solution I'm looking for yet. The inherit value from type D for type C only applies to Type C if Type D has the same ID. Suppose Type A also has the same ID, then it may not have the interval of type D. Type A and others must always keep its own interval value. Is this possible?
Thanks,
@icturion Please try this:-
Result =
VAR _typeD =
CALCULATE (
MAX ( 'Table (3)'[Interval] ),
FILTER (
ALL ( 'Table (3)' ),
'Table (3)'[ID] = EARLIER ( 'Table (3)'[ID] )
&& 'Table (3)'[Type] = "D"
)
)
RETURN
IF (
'Table (3)'[Type] = "C",
IF ( NOT ( ISBLANK ( _typeD ) ), _typeD, 'Table (3)'[Interval] ),
'Table (3)'[Interval]
)
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
@icturion , Try a new column like
new column =
var _1 = countx(filter(Table, [ID] =earlier([ID])), [ID])
var _2 = countx(filter(Table, [ID] =earlier([ID]) && [Type] = "D"), [Interval])
return
Switch(True() ,
_1 <= , [Interval]
_1 >1 && [Type] = "C" , _2 ,
[Interval]
)
Hi @amitchandak ,
Thank you for your respons, if i try your code, i get an error message
The SWITCH function does not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.
The ID column is Whole Number
The Type column is text
The Interval column is decimal
Do you know what i need to change?
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |