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

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.