Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated column with condition

Hello Everyone!

 

First of all, thanks for alwayg being so helpful!

 

I am trying to find a formula to determinate if an order is normal or customized.

 

If my order number contains product type 1 and 2 should be considered as "Custom", otherwise if it only contains 1 should be considered as "Normal order"

 

This is how my table looks:

 

ORDERPRODUCT TYPEORDER TYPE
101CUSTOM
102CUSTOM
121NORMAL
131CUSTOM
132CUSTOM
141NORMAL
151NORMAL
161CUSOTM
162CUSTOM

 

Which formula could I use?

 

Lot of thanks!

 

Need Help
Tips and Tricks
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Anonymous ,

     

    Try this measure:

    Measure 2 =
VAR type_no =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[PRODUCT TYPE] ),
        ALLEXCEPT ( 'Table', 'Table'[ORDER] )
    )
RETURN
    IF ( type_no > 1, "CUSTOM", "NORMAL" )

     

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

3 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    try

    Column =
var _custom = CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[ORDER]), Table[PRODUCT TYPE] = 2)
RETURN
IF (_custom  > 0, "Custom", "Normal order")
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello!

      Thanks for your answer.

       

      I have a problem with the formula as it's indicating order as "Custom" if order has two rows instead of indicating Custom when it has type 2.

       

      How could I fixed?

       

      Thanks!

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Try this measure:

        Measure 2 =
VAR type_no =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[PRODUCT TYPE] ),
        ALLEXCEPT ( 'Table', 'Table'[ORDER] )
    )
RETURN
    IF ( type_no > 1, "CUSTOM", "NORMAL" )

         

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