Forum Discussion

aJ2's avatar
aJ2
Icon for Helper I rankHelper I
8 years ago
Solved

Giving a value to unique id's

I have a table which contains order id's and each order id can have multiple products. 

I want to create a column wherein whenever it encounters the order id, the value 1 is entered into the column else 0 and it also checks the condition if there is only one instance of the order_id.

Below is the screenshot of the sample data.


Below is the screenshot of the desired output 

Any help is highly appreciated. 

Need Help
Show and Tell
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    here is DAX statement that creates a calculated column

    flag = 
var currentOrderID = 'Table1'[order_id]
var minID =
    CALCULATE(
        MIN('Table1'[id])
        ,FILTER(ALL(Table1)
            ,'Table1'[order_id] = currentOrderID
        )
    )
return
IF(AND('Table1'[order_id] = currentOrderID, 'Table1'[id] = minID), 1,0)

    Here is a little screenshot from my tiny sample data :-)

     

    Hopefully this is what you are looking for

     

    Regards

    Tom

     

5 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    Hey,

     

    I do not fully understand your requirement, so I try to rephrase what I understand:

     

    Flag each row with 1 where the order appears the first time, otherwise flag the row with 0.

     

    Given that my understanding is correct, I assume it is also valid to sort the columns by: order_id DESCENDING and id DESCENDING

     

    Do you want this be done in the QueryEditor or done by a DAX statement creating a calculated column.

     

    Regards

    Tom

     

    • aJ2's avatar
      aJ2
      Icon for Helper I rankHelper I
      8 years ago

      You are right. I want to do it using DAX statement

       

      • TomMartens's avatar
        TomMartens
        Icon for Super User rankSuper User
        8 years ago

        Hey,

         

        here is DAX statement that creates a calculated column

        flag = 
var currentOrderID = 'Table1'[order_id]
var minID =
    CALCULATE(
        MIN('Table1'[id])
        ,FILTER(ALL(Table1)
            ,'Table1'[order_id] = currentOrderID
        )
    )
return
IF(AND('Table1'[order_id] = currentOrderID, 'Table1'[id] = minID), 1,0)

        Here is a little screenshot from my tiny sample data :-)

         

        Hopefully this is what you are looking for

         

        Regards

        Tom