Forum Discussion

jiangxm80's avatar
jiangxm80
Helper I
3 years ago
Solved

Add one more columns

I would like to add one more columns, which would be equal to the first line of each set of records.

 

Column1   Column2    Column3

A                RV              100

A                AB                80

A                AB                30

B                RV               200

B                AB               120

B                AB                70 

 

Would expect:

Column1   Column2    Column3   Column4

A                RV              100             100

A                AB                80             100

A                AB                30             100

B                RV               200             200

B                AB               120             200

B                AB                70              200

 

Thanks a lot!

Need Help
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    Hi jiangxm80 ,

     

    For the first question, you can try:

    result = 
VAR _FIRST=CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Column1]))
RETURN LOOKUPVALUE('Table'[Column3],'Table'[Index],_FIRST)

    As for the second question that use the RV:

    You can try this method:

    New column:

    Column =
    CALCULATE (
    MAX ( 'Table'[Column3] ),
    FILTER (
    'Table',
    'Table'[Column2] = "RV"
    && 'Table'[Column1] = EARLIER ( 'Table'[Column1] )
    )
    )

    The result is:

     

     

    Hope this helps you. Here is my PBIX file.

     

     

     

     

    Best Regards,

    Community Support Team _Yinliw

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

6 Replies

  • MahyarTF's avatar
    MahyarTF
    Memorable Member
    3 years ago

    Hi,

    This is my solution :

    1) create another table based on the existing table as below :

    NewTableName = SUMMARIZE(TableName, TableName[Column1]
                          , "Column4", max(TableName[Column3])
                        )
    2) Make a relationship between two tables based on the Column1
    3) Then bring the Column1, Column2,Column3 from first table and Column4 from second one (put the all value on 'Don't summarize'

     

    Appreciate your Kudos and please mark it as a solution if it helps you.

    • jiangxm80's avatar
      jiangxm80
      Helper I
      3 years ago

      Thanks, could I set the logic to get value via Column2, which is RV? 

    • jiangxm80's avatar
      jiangxm80
      Helper I
      3 years ago

      Thanks, could I set the logic to get value via Column2, which is RV? 

    • jiangxm80's avatar
      jiangxm80
      Helper I
      3 years ago

      could you please help me a little bit on this? Thanks

      • v-yinliw-msft's avatar
        v-yinliw-msft
        Community Support
        3 years ago

        Hi jiangxm80 ,

         

        For the first question, you can try:

        result = 
VAR _FIRST=CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Column1]))
RETURN LOOKUPVALUE('Table'[Column3],'Table'[Index],_FIRST)

        As for the second question that use the RV:

        You can try this method:

        New column:

        Column =
        CALCULATE (
        MAX ( 'Table'[Column3] ),
        FILTER (
        'Table',
        'Table'[Column2] = "RV"
        && 'Table'[Column1] = EARLIER ( 'Table'[Column1] )
        )
        )

        The result is:

         

         

        Hope this helps you. Here is my PBIX file.

         

         

         

         

        Best Regards,

        Community Support Team _Yinliw

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