locked
Use relatedtable funtion in an IF statement RRS feed

  • Question

  • I have created two linked tables (table 1) and (table 2) in power pivot and created a relationship between the columns "Account Name" found in table 1 and table 2.

    I need to create a function similar to <g class="gr_ gr_210 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="210" id="210">vlookup</g> in table 2 that will fetch values from table 1.

    Table 2 is a power pivot table showing the <g class="gr_ gr_300 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="300" id="300">agreggation</g> (count of rows) and (sum of "received") based on "account name" column in table 1.

    I'm trying this formula:

    =if(table2(count of rows)>1,"Multiple values",relatedtable(table1[campaign]))

    It's giving me an error saying:

    

    Please advise on how to create this formula properly in power pivot.

    Thanks.

    Thursday, October 4, 2018 7:50 AM

All replies

  • I don't understand your ggr_stuff and not clear on which if the many table.

    Your formula essentially says if there are multiple rows in table two, return the constant "Multiple Values" otherwise return the "campaign column" value for table 1. 

    SO I am suggesting

    1) the first element is what is in table two for this row in table 1

            MATCHING_ROWS = COUNTROWS(RELATEDTABLE(TABLE2)) 

    This returns a number 0..N. There are three logical results, NONE, ONE, MANY

    2) IF(MATCHING_ROWS = 0, THEN "NO OTHER CAMPAIGNS"  --- the NONE option

    3) IF(MATCHING ROWS > 1, THEN "MULTIPLE CAMPAIGNS"  --- the MANY option

    4) IF(MATCHING ROWS = 1, THEN TABLE1[CAMPAIGN] -- what we already knew

     If it were to be a column definition like this since you already know the current campaign in table1. Then something like this...

    TABLE1[CAMPAIGN STATUS]=IF(COUNTROWS(RELATEDTABLE(TABLE2))=0,"NO OTHER CAMPAIGN FOUND",

       IF(COUNTROWS(RELATEDTABLE(TABLE2))>1,"MULTIPLE CAMPAIGNS FOUND",

          TABLE1[CAMPAIGN]))

    This could also be done with SWITCH, the the count and set up the 0,1 result pairs and else is the many. Easier to read.

    TABLE1[CAMPAIGN STATUS]= SWITCH(COUNTROWS(RELATEDTABLE(TABLE2)),

        0, "NO OTHER CAMPAIGNS FOUND",,

        1, TABLE1[CAMPAIGN],

        "MULTIPLE CAMPAIGNS FOUND")

    If you are making a measure you need to deal with context since you want to iterate row by row in table1. That's a different issue and more than I can do at this point without more help from others.


    • Edited by geraldartman Wednesday, October 31, 2018 10:34 AM
    Wednesday, October 31, 2018 10:31 AM