locked
DAX formula for - Extracting a column value from a table based on certain conditions RRS feed

  • Question

  • Hi All,

    I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.

    Here is the scenario:

    I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).

    I would like to extract  Key_member where key_member_type="PM" and active as of tb1(month_end_date).

    Is this possible using DAX ?

    Thanks,
    Satya T
    Monday, July 20, 2015 8:21 PM

Answers

  • Hi Satya,

    According to your description, you need to extract a column value from another table base on certain conditions in your PowerPivot data model, right?

    If this is the case, then you can use LOOKUPVALUE function which returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value. The DAX expression looks like:
    =LOOKUPVALUE(tb2[KeyMember],tb2[KeyMemberType,"PM")
    For the details about, this function, please refer to the link below.
    https://msdn.microsoft.com/en-us/library/gg492170.aspx?f=255&MSPPError=-2147217396

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Charlie Liao Wednesday, August 5, 2015 2:05 AM
    Tuesday, July 21, 2015 9:52 AM

All replies

  • (how) are your 2 tables related?

    Imke

    Monday, July 20, 2015 8:38 PM
    Answerer
  • Hi Imke,

    They are logically related (project_id). It is a many to many relation-ship

    We have same project_id for each month and we have same project_id for multiple project key members.

    Thanks,

    Monday, July 20, 2015 8:52 PM
  • Excel 2010 with free PowerPivot and Power Query Add-In.
    Compatible with Office 2013 Pro Plus.
    PP and PQ are better integrated in 2013.
    If my fictitious data and format is not an adequate fit, share your file.
    http://www.mediafire.com/view/zmddi0bhgdqzcf8/07_20_15.xlsx

    Monday, July 20, 2015 11:08 PM
  • Hi Herbert,

    Thanks for taking time and putting together sample data.
    Following is the output I'm expecting using DAX formula. I do not want to create a Cartesian join

    PRJ_ID    Mo_End             Cost             AM                                      PM
    DIS    01/31/14     $65.22     Taylor or
                                            White or Thomas
                                           (Only one - prefereably Thomas as most recent)                                                                                                   Williams
    DIS    02/28/14     $85.15     Thomas                                          Anderson
    DIS    04/30/14     $60.52     Davis                                             blank()
    DIS    05/31/14     $68.62         
    DIS    06/30/14     $33.66         
    DIS    07/31/14     $10.16         
    DIS    09/30/14     $52.25         
    DIS    12/31/14     $39.77         

    Thanks,
    Tuesday, July 21, 2015 2:18 AM
  • Tweaked Power Query a bit.
    "...most recent..." not implemented
    but left as an exercise for the gentle reader.
    Same link.

    Tuesday, July 21, 2015 4:29 AM
  • Hi Satya,

    According to your description, you need to extract a column value from another table base on certain conditions in your PowerPivot data model, right?

    If this is the case, then you can use LOOKUPVALUE function which returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value. The DAX expression looks like:
    =LOOKUPVALUE(tb2[KeyMember],tb2[KeyMemberType,"PM")
    For the details about, this function, please refer to the link below.
    https://msdn.microsoft.com/en-us/library/gg492170.aspx?f=255&MSPPError=-2147217396

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Charlie Liao Wednesday, August 5, 2015 2:05 AM
    Tuesday, July 21, 2015 9:52 AM