Forum Discussion

yuyu18's avatar
yuyu18
Regular Visitor
3 years ago
Solved

Get value when first time value equal to

Hi, Im very new to DAX so some structure I'm not really understand. Can you help me: how to get value1 when value1 first time equal to value2?
For example, I wanna get Point when Point = 8 for the first time. And when Point >= 8, output "Safe" && Point < 6 then "Caution" for the first time Point reach 6 and 8

  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, yuyu18 

     

    Let's say you have a table called "MyTable" with two columns: "Point" and "Status". You want to create a calculated column called "NewStatus" that will return "Safe" when Point >= 8 for the first time, "Caution" when Point >= 6 for the first time but less than 8, and blank otherwise.

     

    To do this, you can use the following DAX formula:

    NewStatus =
IF (
    AND ( Point >= 8, NOT ( "Safe" IN MyTable[Status] ) ),
    "Safe",
    IF (
        AND ( Point >= 6, Point < 8, NOT ( "Caution" IN MyTable[Status] ) ),
        "Caution",
        BLANK ()
    )
)

    This formula uses the IF function to check if the conditions are met for each status, and returns the appropriate value. The NOT function is used to check if the status has already been assigned to a previous row, so that it only applies to the first occurrence of each status.

     

    Please check if you can produce the results you expect. If not, please provide example data and desired output.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, yuyu18 

     

    Let's say you have a table called "MyTable" with two columns: "Point" and "Status". You want to create a calculated column called "NewStatus" that will return "Safe" when Point >= 8 for the first time, "Caution" when Point >= 6 for the first time but less than 8, and blank otherwise.

     

    To do this, you can use the following DAX formula:

    NewStatus =
IF (
    AND ( Point >= 8, NOT ( "Safe" IN MyTable[Status] ) ),
    "Safe",
    IF (
        AND ( Point >= 6, Point < 8, NOT ( "Caution" IN MyTable[Status] ) ),
        "Caution",
        BLANK ()
    )
)

    This formula uses the IF function to check if the conditions are met for each status, and returns the appropriate value. The NOT function is used to check if the status has already been assigned to a previous row, so that it only applies to the first occurrence of each status.

     

    Please check if you can produce the results you expect. If not, please provide example data and desired output.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.