Suggestions to get the join between Objectives and Key Results working?

[jch]

In this Power Bi, I'm trying to write something like the statement below, both tables filtered on the field IsCurrent.

Does anyone have any suggestions to write the Dax table statement correctly?

DaxTable = SUMMARIZE(
'Objectives'
, 'Objectives'[IsCurrent]
, 'Objectives'[Work Item ID]
, 'Key Results'[Parent Work Item ID]
, 'Objectives'[Title]
, 'Key Results'[Work Item ID]
, 'Key Results'[IsCurrent]
, 'Key Results'[Title]
, 'Key Results'[Jan Decimal X Weight]
, 'Key Results'[Feb Decimal X Weight]
, 'Key Results'[Mar Decimal X Weight]
)

Ideally, I want to create one Dax table that's similar to the format and filters like what's displayed in this visual

[bcdobbs]

I think most of the trouble you're having is that you have repeated columns of the same name. Could work around it using SELECTCOLUMNS first. However I renamed 3 columns in the Objectives table to "Objective Work Item ID", "Objective Title" and "Objective IsCurrent". You could match in Key Results for consistency.

Then the following DAX will give you what you need including the filters.

JoinTable = 
VAR ObjectivesFilter =
        TREATAS ( { TRUE }, 'Objectives'[Objective IsCurrent] )
VAR KeyResultsFilter =
        TREATAS ( { TRUE }, 'Key Results'[IsCurrent] )
RETURN 
SUMMARIZECOLUMNS (
    'Objectives'[Objective Work Item ID],
    'Key Results'[Parent Work Item ID],
    'Key Results'[Work Item ID],
    'Objectives'[Objective Title],
    'Key Results'[Title],
    'Objectives'[Objective IsCurrent],
    'Key Results'[IsCurrent],
    ObjectivesFilter,
    KeyResultsFilter,
    "SumJan_Decimal_X_Weight", CALCULATE ( SUM ( 'Key Results'[Jan Decimal X Weight] ) ),
    "SumFeb_Decimal_X_Weight", CALCULATE ( SUM ( 'Key Results'[Feb Decimal X Weight] ) ),
    "SumMar_Decimal_X_Weight", CALCULATE ( SUM ( 'Key Results'[Mar Decimal X Weight] ) )
)

[jch]

Thank you for your reply. Do I need a relatedtable function in this so that only the rows where 'Objectives'[Work Item Id] = 'Key Results'[Parent Work Item ID]. How do I work around the error in the screenshot below? 

[askhanduja]

DaxTable = 
CALCULATETABLE(
SUMMARIZE(
        'Key Results'
        , 'Objectives'[IsCurrent]
        , 'Objectives'[Work Item ID]
        , 'Key Results'[Parent Work Item ID]
        , 'Objectives'[Title]
        , 'Key Results'[Work Item ID]
        , 'Key Results'[IsCurrent]
        , 'Key Results'[Title]
        , 'Key Results'[Jan Decimal X Weight]
        , 'Key Results'[Feb Decimal X Weight]
        , 'Key Results'[Mar Decimal X Weight]
    ),
    Objectives[IsCurrent] = TRUE(),
    'Key Results'[IsCurrent] = TRUE()
)

If this answers your question please mark it as the answer so that others with a similar question can find the post easily. A thumbs up to the post would be greatly appreciated as well.

[jch]

Thank you for your reply. Thank you for your reply. Do I need a relatedtable function in this so that only the rows where 'Objectives'[Work Item Id] = 'Key Results'[Parent Work Item ID]. How do I work around the error in the screenshot below?