Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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] ) )
)
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?
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.
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |