Forum Discussion
Measure used in MAX statement
DEFINE
MEASURE 'DAX BSS Measures'[Test_measureNEW] =
VAR date_field =
MAX ( STG_JIRA_ISSUE[Created] )
VAR date_value =
DATE ( YEAR ( date_field ), MONTH ( date_field ), DAY ( date_field ) )
VAR release_date =
CALCULATE (
MIN ( CONF_SPRINT_CALENDAR[Release Date] ),
FILTER (
CONF_SPRINT_CALENDAR,
CONF_SPRINT_CALENDAR[Start Date] <= date_value
&& CONF_SPRINT_CALENDAR[Release Date] >= date_value
&& CONF_SPRINT_CALENDAR[Country]
= MAX ( STG_JIRA_ISSUE[Country (Extracted) Sprint] )
&& CONF_SPRINT_CALENDAR[Project Key] IN { "BS", "SEL" }
)
)
VAR filter_defects =
FILTER (
ADDCOLUMNS (
STG_JIRA_ISSUE,
"Defect Sprint Date", DATE ( YEAR ( release_date ), MONTH ( release_date ), 1 )
),
[Defect Sprint Date] = MAX ( [Defect Sprint Date] )
)
VAR bss_sweden_defects =
CALCULATE (
COUNT ( STG_JIRA_ISSUE[ID] ),
filter_defects,
STG_JIRA_ISSUE[Country (Extracted) Sprint] = "Sweden"
)
RETURN
bss_sweden_defects
EVALUATE
FILTER (
ADDCOLUMNS ( STG_JIRA_ISSUE, "Number of Defects", [Test_measureNEW] ),
NOT ISBLANK ( [Number of Defects] )
)
My logic in this code was following:
1. Based on Created dated find Sprint Release Date;
2. Then based on Release Date (only Month is needed) count number of Issues per Month.
Result of my query is error: "Table 'DAX BSS Measures' cannot be used in computations because it does not have any columns."
Issue is in Variable filter_defects, DAX doesn't like MAX( [Defect Sprint Date] ), which is separate column created within FILTER.
Is there solution, how to solve my issue?
Hey,
there are some things you have to consider.
You can't use ADDCOLUMN(...) to add a column to an already exisiting table 'STG_Jira_Issue'.
I'm not sure from where the table originates, if it's a table that you compose with SSDT you might consider to add a CALCULATED COLUMN, if it's a table that you "import" into Power BI, create a calculated column.
Hopefully this gives you an idea.
Regards,
Tom
2 Replies
- TomMartensSuper User
Hey,
there are some things you have to consider.
You can't use ADDCOLUMN(...) to add a column to an already exisiting table 'STG_Jira_Issue'.
I'm not sure from where the table originates, if it's a table that you compose with SSDT you might consider to add a CALCULATED COLUMN, if it's a table that you "import" into Power BI, create a calculated column.
Hopefully this gives you an idea.
Regards,
Tom
- AnonymousNot applicable
i have issue...i want to calculate number of workitems that were removed from the second day of the sprint till the end of the end of the sprint . i have 2 different columns ...one is for sprints and another is for workitems...
Can you help me with this?