Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

  • 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

  • Anonymous's avatar
    Anonymous
    Not 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?