Forum Discussion

bidav's avatar
bidav
Frequent Visitor
4 years ago

new column based on values in rows (direct query)

hi guys

i am trying to create a new column, named "startedyn" that will display "STARTED" on all the rows within the same "docentry" number, if one of the rows has "1" value in the "start_no" column. in my screenshot, not all the "start_no" values have 1. one of them has 0, but still the new column should display started on all the rows within the same docentry number. The dataset is direct query so i can't use something like startedyn = if (CALCULATE( MAX(Table[start_no]) ,ALLEXCEPT(Table,Table[docentry] )) > 0, "STARTED")

thank you! and sorry for the noob question. new to power bi!

Need Help
Tips and Tricks

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    bidav , I doubt you can create a column like that in direct query, you should create a measure

    • bidav's avatar
      bidav
      Frequent Visitor
      4 years ago

      hi, thank you for your reply.

      how can i create a measure that will modify the column mentioned with "started" isn't measure is just a single number?

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, bidav 

     

    You can try the following methods.

    Column:

    startedyn = IF(CALCULATE(MIN('Table'[start_no]),ALLEXCEPT('Table','Table'[docentry]))>0,"started")

    The formula for Measure is the same as the formula for calculating columns.

     

    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.