Add column to Power Bl tab where value is from another row in table where 3 columns match criteria

[Gangsta]

In the table below, records 1,2,3,5 DAX formula copies city names from Town to the Missing Town as data was already available.

							New Column
Number	First Name	Second Name	Birthday	Visits	Distinct	Town	Missing Town
1	Jack	Dee	16/06/2011	0	2	London	London
2	Paul	Jacob	01/07/2014	0	1	Paris	Paris
3	Peter	James	07/03/2015	1	2	Madrid	Madrid
4	Viktors	Thomas	27/12/2008	1	1
5	Eliana	Plumb	05/02/2012	1	1	Miami	Miami
6	Kausar	Defoe	10/10/2008	0	1
7	Khadija	Singh	19/11/2011	1	1	Tokyo	Miami
8	Krish	King	24/07/2008	0	1
9	Ibrar	Plaice	02/09/2011	1	1
10	Nash	Chow	07/05/2008	0	1
11	Jack	Dee	16/06/2011	1	2		London
12	Ahmad	Santosh	19/02/2008	0	1
13	Peter	James	07/03/2015	0	2

records 4,6,8,9,10,12 and 13 do not have the Town and as there are no other matching records with Town that meet the 3 criteria First Name, Second name and Birthday , so DAX formula will leave them empty on the Missing Town Column, unless we can find another record that matches the 3 cirtiera.

DAX formula is needed for 'Missing Town' column so Record 11 can pull Town into the Missing Town column from record 1 that matches the 3 criteria which has the Town.

Hope that helps claridy what I am trying to do, bnasicall pupulating a colum if the data is available in one ofthe other records in the table if 3 criteria are met.

[v-jiewu-msft · Accepted solution]

Hi @Gangsta ,

Based on my testing, please try the following DAX formula:

1.Create the new column.

Missing Town = 
VAR CurrentFirstName = 'Table'[First Name]
VAR CurrentSecondName = 'Table'[Second Name]
VAR CurrentBirthday = 'Table'[Birthday]
RETURN
    CALCULATE(
        MAX('Table'[Town]),
        FILTER(
            'Table',
            'Table'[First Name] = CurrentFirstName &&
            'Table'[Second Name] = CurrentSecondName &&
            'Table'[Birthday] = CurrentBirthday &&
            'Table'[Town] <> BLANK()
        )
    )

2.The result is shown below.

Best Regards,

Wisdom Wu

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

[v-jiewu-msft · Accepted solution]

Hi @Gangsta ,

Based on my testing, please try the following DAX formula:

1.Create the new column.

Missing Town = 
VAR CurrentFirstName = 'Table'[First Name]
VAR CurrentSecondName = 'Table'[Second Name]
VAR CurrentBirthday = 'Table'[Birthday]

VAR FindMaxVisits =
    CALCULATE(
        MAX('Table'[Visits]),
        ALLEXCEPT('Table', 'Table'[First Name], 'Table'[Second Name], 'Table'[Birthday]),
        'Table'[Town] <> BLANK()
    )
VAR FindTownWithMaxVisits =
    CALCULATE(
        MAX('Table'[Town]),
        //FIRSTNONBLANK('Table'[Town], 1),
        FILTER(
            'Table',
            'Table'[First Name] = CurrentFirstName &&
            'Table'[Second Name] = CurrentSecondName &&
            'Table'[Birthday] = CurrentBirthday &&
            'Table'[Visits] = FindMaxVisits
        )
    )
RETURN
IF(
    'Table'[Town] <> BLANK(),
    'Table'[Town], 
    FindTownWithMaxVisits
)

2.The result is shown below.

Best Regards,

Wisdom Wu

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

[v-jiewu-msft · Accepted solution]

Hi @Gangsta ,

Based on my testing, please try the following DAX formula:

1.Create the new column.

Missing Town = 
VAR CurrentFirstName = 'Table'[First Name]
VAR CurrentSecondName = 'Table'[Second Name]
VAR CurrentBirthday = 'Table'[Birthday]
RETURN
    CALCULATE(
        MAX('Table'[Town]),
        FILTER(
            'Table',
            'Table'[First Name] = CurrentFirstName &&
            'Table'[Second Name] = CurrentSecondName &&
            'Table'[Birthday] = CurrentBirthday &&
            'Table'[Town] <> BLANK()
        )
    )

2.The result is shown below.

Best Regards,

Wisdom Wu

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

[Gangsta]

Sorry I have 1 further requirement, sorry for pushing my luck but it would be great if the DAX column called Missing Town also did  the following, 

If the Town is known, then that should be copied to Missing Town, only if the Town is unknown it proceeds to solution provided, but if the solution can now check if ther are multiple other records that have the town, then the record that has most visits where the town is copied from.

In the below updated table

Green records copied over the town to New town since the Town was already know

Red Records there were two for the same person, number 1 had the Town, number 11 did not, so it was copied from 1, as per the existing solution.

Now there are 3 records for Peter James 3, 9 and 13, 3 has the town so it is copied to Missing Town as Madrid.  Number 9 also has the Town Beijing, this is copied to Missing Town.  Number 13 does not have the Town, there are two choices for the Missing Town, either Madrid or Beijing, we want to copy the one with the most visits, so Beijing is copied as that has 3 visits.   

							New Column
Number	First Name	Second Name	Birthday	Visits	Distinct	Town	Missing Town
1	Jack	Dee	16/06/2011	0	2	London	London
2	Paul	Jacob	01/07/2014	0	1	Paris	Paris
3	Peter	James	07/03/2015	1	3	Madrid	Madrid
4	Viktors	Thomas	27/12/2008	1	1
5	Eliana	Plumb	10/08/2015	1	1	Miami	Miami
6	Kausar	Defoe	10/10/2008	0	1
7	Khadija	Singh	24/05/2015	1	1	Tokyo	Tokyo
8	Krish	King	24/07/2008	0	1
9	Peter	James	07/03/2015	4	3	Beijing	Beijing
10	Nash	Chow	07/05/2008	0	1
11	Jack	Dee	16/06/2011	1	2		London
12	Ahmad	Santosh	19/02/2008	0	1
13	Peter	James	07/03/2015	0			Beijing

Would be great if this adjustment can be made. 

Thanks again,

[v-jiewu-msft · Accepted solution]

Hi @Gangsta ,

Based on my testing, please try the following DAX formula:

1.Create the new column.

Missing Town = 
VAR CurrentFirstName = 'Table'[First Name]
VAR CurrentSecondName = 'Table'[Second Name]
VAR CurrentBirthday = 'Table'[Birthday]

VAR FindMaxVisits =
    CALCULATE(
        MAX('Table'[Visits]),
        ALLEXCEPT('Table', 'Table'[First Name], 'Table'[Second Name], 'Table'[Birthday]),
        'Table'[Town] <> BLANK()
    )
VAR FindTownWithMaxVisits =
    CALCULATE(
        MAX('Table'[Town]),
        //FIRSTNONBLANK('Table'[Town], 1),
        FILTER(
            'Table',
            'Table'[First Name] = CurrentFirstName &&
            'Table'[Second Name] = CurrentSecondName &&
            'Table'[Birthday] = CurrentBirthday &&
            'Table'[Visits] = FindMaxVisits
        )
    )
RETURN
IF(
    'Table'[Town] <> BLANK(),
    'Table'[Town], 
    FindTownWithMaxVisits
)

2.The result is shown below.

Best Regards,

Wisdom Wu

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

[Gangsta · Accepted solution]

Thanks @v-jiewu-msft, I will try this on the report and get back to you, again appreciate your help.

[Gangsta]

Just wanted give a huge thanks, your solution worked perfectly without any changes needed so have marked it as the solution.  You saved me alot of time especially as I was not sure how to  phrase it in my question being new to DAX I dont know all the various possible methods, the onese I was trying were compledtelty different to your appraoch so foes without saying you helped me incrfease my understanding, but still long way to go, thank you.

[FreemanZ]

hi @Gangsta ,

try like:

Column =

VAR _table =

CALCULATEDTABLE(

    data,

    ALLEXCEPT(data, [First Name], [Second Name], [Birthday])

)

VAR _town = MAXX(_table, data[Town])

RETURN _town