How to create a new calculated column with a condition

Hi,

I am not sure if I understood your question corretly, but please check the below picture and the attached pbix file.

expected result calculated column = 
VAR _username = data[User Name]
VAR _na =
    FILTER ( data, data[User Name] = _username && data[Status] = "N/A" )
VAR _join =
    FILTER ( data, data[User Name] = _username && data[Status] = "Join" )
VAR _primary =
    FILTER ( data, data[User Name] = _username && data[Status] = "Primary" )
VAR _result =
    SWITCH (
        TRUE (),
        COUNTROWS ( _na ) <> 0, SELECTCOLUMNS ( _na, data[Type] ),
        COUNTROWS ( _join ) <> 0, SELECTCOLUMNS ( _join, data[Type] ),
        COUNTROWS ( _primary ) <> 0, SELECTCOLUMNS ( _primary, data[Type] )
    )
RETURN
    _result

Hi POSPOS,
Thank you for reaching out to the Microsoft fabric community forum. Thank you Jihwan_Kim, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Measure for creating column in table :

New Column =

VAR CurrentUser = 'Table'[User Name]



-- Get the "Type" where Status is "Primary"

VAR PrimaryType =

    CALCULATE(

        MAX('Table'[Type]),

        FILTER('Table', 'Table'[User Name] = CurrentUser && 'Table'[Status] = "Primary")

    )



-- If no "Primary", get the "Type" where Status is "Join"

VAR JoinType =

    CALCULATE(

        MAX('Table'[Type]),

        FILTER('Table', 'Table'[User Name] = CurrentUser && 'Table'[Status] = "Join")

    )



-- If no "Primary" or "Join", get the "Type" where Status is "N/A"

VAR NAType =

    CALCULATE(

        MAX('Table'[Type]),

        FILTER('Table', 'Table'[User Name] = CurrentUser && 'Table'[Status] = "N/A")

    )



-- Return value based on priority: Primary > Join > N/A

RETURN

    IF(

        NOT ISBLANK(PrimaryType), PrimaryType,

        IF(

            NOT ISBLANK(JoinType), JoinType,

            NAType

        )

    )

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.