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

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 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