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
icturion
Resolver II
Resolver II

calculated column based on row values

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:

IDTypeInterval
123A1
234B2
345C3
345D4
768C3
987D4

 

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

IDTypeIntervalResult
123A11
234B22
345C34
345D44
768C33
987D44

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

1 ACCEPTED 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

View solution in original post

5 REPLIES 5
Samarth_18
Community Champion
Community Champion

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:-

Samarth_18_0-1644308115553.png

 

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

amitchandak
Super User
Super User

@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?

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.