Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Copy Values

Hello Team,

 

I have the following data on my report as a matrix in PBI Desktop

 

IDCodeNameValue1Value2Value3
5670670003Apples   
 578080Apples407839

 

The ID, Code and Name columns are expanded. I have no Values present for the code 670003. How can I write a calculation to copy the value from the other Code 578080 to the first line. 

 

Output :

 

IDCodeNameValue1Value2Value3
5670670003Apples407839
 578080Apples407839

 

Thanks

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Anonymous 

    According to your description, I created the following two tables:

    I use the following DAX expression, which mainly uses the lookup value to obtain the code of another table, so that the number of id, code, and name can be matched:

     

    Table 2=
VAR _table =
    ADDCOLUMNS (
        ADDCOLUMNS (
            SELECTCOLUMNS ( 'Table', 'Table'[Key], 'Table'[Name], 'Table'[Code] ),
            "Id", LOOKUPVALUE ( 'Table 2 (2)'[Code], 'Table 2 (2)'[ID], 'Table'[Key] )
        ),
        "Value1", [Measure1],
        "Value2", [Measure2],
        "Value3", [Measure3]
    )
RETURN
    ADDCOLUMNS (
        _table,
        "Value1_new",
            VAR _id = [Id]
            RETURN
                IF (
                    ISBLANK ( [Value1] ),
                    FIRSTNONBLANK (
                        SELECTCOLUMNS ( FILTER ( _table, [Id] = _id ), [Value1] ),
                        [Value1]
                    ),
                    [Value1]
                ),
        "Value2_new",
            VAR _id = [Id]
            RETURN
                IF (
                    ISBLANK ( [Value2] ),
                    FIRSTNONBLANK (
                        SELECTCOLUMNS ( FILTER ( _table, [Id] = _id ), [Value2] ),
                        [Value2]
                    ),
                    [Value2]
                ),
        "Value3_new",
            VAR _id = [Id]
            RETURN
                IF (
                    ISBLANK ( [Value3] ),
                    FIRSTNONBLANK (
                        SELECTCOLUMNS ( FILTER ( _table, [Id] = _id ), [Value3] ),
                        [Value3]
                    ),
                    [Value3]
                )
    )

     

     

    I have included the PBIX file used for this tutorial below.

     

     

     

     

     

     

    Best Regards

    Jianpeng Li

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

17 Replies

  • dharmendars007's avatar
    dharmendars007
    Memorable Member
    1 year ago

    Hello Anonymous , 

     

    The best way i think of is using fill up option in Power Query so that values which are presnet in below row will get filled up in above row and vice-versa if you are looking for to copy data below..Aprt from this is there conditions in your logic where you want to copy the data.

     

     

     

    If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

     

    Thank You

    Dharmendar S

    LinkedIN 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      dharmendars007 Thanks I do not want to backfill data in power query but write a measure or calculated column to fill the data in the report. How can this be done?

       

      Yes the condition can be if same ID in this case 5670 then fill up data. Something like if Values are blank and ID is same then fill the values based on the existing number like line 2 in this case.

       

      Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      dharmendars007 Actually fill up or fill down does not add new values for the missing fields. Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thank you dharmendars007 

    Hi, Anonymous 

    Based on your description, I use the following example data:

    I've created some calculated columns using the following DAX expression:

    Column1 = 
VAR _id = 'Table'[Id]
RETURN
IF(
    ISBLANK('Table'[Value1]),
    FIRSTNONBLANKVALUE(SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Id]=_id),'Table'[Value1]),MAX('Table'[Value1])),
    'Table'[Value1]
)
    Column2 = 
VAR _id = 'Table'[Id]
RETURN
IF(
    ISBLANK('Table'[Value2]),
    FIRSTNONBLANKVALUE(SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Id]=_id),'Table'[Value2]),MAX('Table'[Value2])),
    'Table'[Value2]
)
    Column3 = 
VAR _id = 'Table'[Id]
RETURN
IF(
    ISBLANK('Table'[Value3]),
    FIRSTNONBLANKVALUE(SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Id]=_id),'Table'[Value3]),MAX('Table'[Value3])),
    'Table'[Value3]
)

    Here are the results:

    Then use these calculated columns to create a matrix:

    I've uploaded the PBIX file I used this time below.

     

     

    Best Regards

    Jianpeng Li

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Anonymous  Thanks, However I am stuck with the below issue

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi, Anonymous 

        I update my DAX expression:

        Table 2 =
VAR _table =
    ADDCOLUMNS (
        ALL ( 'Table'[Id], 'Table'[Code], 'Table'[Name] ),
        "Value1", [Measure1],
        "Value2", [Measure2],
        "Value3", [Measure3]
    )
RETURN
    ADDCOLUMNS (
        _table,
        "Value1_new",
            VAR _id = 'Table'[Id]
            RETURN
                IF (
                    ISBLANK ( [Value1] ),
                    FIRSTNONBLANK (
                        SELECTCOLUMNS ( FILTER ( _table, 'Table'[Id] = _id ), [Value1] ),
                        [Value1]
                    ),
                    [Value1]
                ),
        "Value2_new",
            VAR _id = 'Table'[Id]
            RETURN
                IF (
                    ISBLANK ( [Value2] ),
                    FIRSTNONBLANK (
                        SELECTCOLUMNS ( FILTER ( _table, 'Table'[Id] = _id ), [Value2] ),
                        [Value2]
                    ),
                    [Value2]
                ),
        "Value3_new",
            VAR _id = 'Table'[Id]
            RETURN
                IF (
                    ISBLANK ( [Value3] ),
                    FIRSTNONBLANK (
                        SELECTCOLUMNS ( FILTER ( _table, 'Table'[Id] = _id ), [Value3] ),
                        [Value3]
                    ),
                    [Value3]
                )
    )

         

        Best Regards

        Jianpeng Li

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Anonymous  Thanks!

     

    However, I get the below error

     

     

    Please note Value 1 is a calculated measure

     

    Value1 = DIVIDE(SUM('Sales'[Net_Sales]),SUM('Qty'[Quantity]))
     
    Please advise
     
    Thanks

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Anonymous THanks

     

    I get the below issue

     

    The Value1 field is a calculated measure where I divide one value by another. Also ID is actually from another table.

     

    Can you please advise? Thanks!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, Anonymous 

      If you're a measure, you should create a new calculated table with your measure, using my previous example data, where I used three measures and then used them to construct a calculated table.
      On the constructed calculation table, I added the columns you need, which are basically the same as the previous one, and the following is the DAX expression I used:

       

      Table 2 =
ADDCOLUMNS (
    ADDCOLUMNS (
        ALL ( 'Table'[Id], 'Table'[Code], 'Table'[Name] ),
        "Value1", [Measure1],
        "Value2", [Measure2],
        "Value3", [Measure3]
    ),
    "Value1_new",
        VAR _id = 'Table'[Id]
        RETURN
            IF (
                ISBLANK ( [Value1] ),
                FIRSTNONBLANKVALUE (
                    SELECTCOLUMNS ( FILTER ( ALL ( 'Table' ), 'Table'[Id] = _id ), 'Table'[Value1] ),
                    MAX ( 'Table'[Value1] )
                ),
                [Value1]
            ),
    "Value2_new",
        VAR _id = 'Table'[Id]
        RETURN
            IF (
                ISBLANK ( [Value2] ),
                FIRSTNONBLANKVALUE (
                    SELECTCOLUMNS ( FILTER ( ALL ( 'Table' ), 'Table'[Id] = _id ), 'Table'[Value2] ),
                    MAX ( 'Table'[Value2] )
                ),
                [Value2]
            ),
    "Value3_new",
        VAR _id = 'Table'[Id]
        RETURN
            IF (
                ISBLANK ( [Value3] ),
                FIRSTNONBLANKVALUE (
                    SELECTCOLUMNS ( FILTER ( ALL ( 'Table' ), 'Table'[Id] = _id ), 'Table'[Value2] ),
                    MAX ( 'Table'[Value2] )
                ),
                [Value3]
            )
)

       

      Here's the calculated table generated by this expression:

      I've provided the PBIX file used this time below.

       

       

      Best Regards

      Jianpeng Li

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

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    You can write a measure:
    Value1_Fill =
    IF (
    ISBLANK ( MAX(YourTable[Value1]) ),
    CALCULATE (
    MAX(YourTable[Value1]),
    FILTER (
    YourTable,
    YourTable[Name] = MAX(YourTable[Name]) &&
    NOT(ISBLANK(YourTable[Value1]))
    )
    ),
    MAX(YourTable[Value1])
    )


    Similarly write for Value2 and Value3.

    If this post helps, please consider Accepting it as the solution to help the other members find it more quickly and  Your Kudos/Likes are much appreciated!

     

    Regards,

    Kedar Pande

    www.linkedin.com/in/kedar-pande





    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Kedar_Pande  Thanks I get this error "The MAX function only accepts a column reference as the argument number 1."

      • Kedar_Pande's avatar
        Kedar_Pande
        Super User
        1 year ago

        Here’s the updated solution using SELECTEDVALUE():

        Value1_Fill =
        IF (
        ISBLANK ( SELECTEDVALUE(YourTable[Value1]) ),
        CALCULATE (
        MAX(YourTable[Value1]),
        FILTER (
        YourTable,
        YourTable[Name] = SELECTEDVALUE(YourTable[Name]) &&
        NOT(ISBLANK(YourTable[Value1]))
        )
        ),
        SELECTEDVALUE(YourTable[Value1])
        )