Copy Values

[Anonymous]

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

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 :

ID	Code	Name	Value1	Value2	Value3
5670	670003	Apples	40	78	39
	578080	Apples	40	78	39

Thanks

[Kedar_Pande]

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]

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

[Kedar_Pande]

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

[Anonymous]

@Thanks I get "Parameter not correct type" for the measure. The Measure is calculated by dividing 2 columns.

[Anonymous]

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

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. 

[Anonymous]

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

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]

@Anonymous  Thanks, However I am stuck with the below issue

[Anonymous]

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]

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

Hi, @Anonymous 

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

Best Regards

Jianpeng Li

[Anonymous]

@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 · Accepted solution]

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.

[dharmendars007]

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]

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

[Anonymous]

@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