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   ID Code Name Value1 Value2 Value3 5670 670003 Apples         578080 Apples 40 78 39  ...
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.

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 Thank you it works but I have an inflated number

 

 

Left is old column and right is new...also please note the ID and Code come from different tables. 

 

Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Anonymous 

    Could you, taking my sample data, describe what your second table looks like? How are they related?

     

     

    Best Regards

    Jianpeng Li

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Anonymous I have created the same table like in your example. The are independent without a join (i.e Table and Table 2). However, in my model the Table is connected to a calendar and also another table based on the ID Primary key. Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.