paginated Report Query Editor help required.

[ashmitp869]

Hi All,

I have requriement in Paginated Report where I am using 4 tables to join 

PayItem, Projects, Date and BilledRevenueDetail.

My Dax Query Editor giving me this below error. please help.

My Code

EVALUATE
VAR PayItemProjects =
    ADDCOLUMNS(
        SUMMARIZE(
            PayItem,
            PayItem[PayItemId],
            PayItem[PayItemNumber],
            PayItem[Description],
            PayItem[UnitofMeasureName],
            PayItem[ForecastTakeOffQuantity],
            PayItem[ForecastUnitRevenue],
            PayItem[Project_id],
            "SumForecastFinalRevenue", SUM(PayItem[ForecastFinalRevenue])
        ),
        "ProjectName", LOOKUPVALUE(Projects[ProjectName], Projects[Project_id], PayItem[Project_id])
    )

VAR BilledPayItem =
    ADDCOLUMNS(
        SUMMARIZE(
            BilledRevenueDetail,
            BilledRevenueDetail[PayItemId],
            BilledRevenueDetail[BilledDate]
        ),
        "SumBilledRevenue", CALCULATE(SUM(BilledRevenueDetail[BilledRevenue])),
        "BilledQuantity", CALCULATE(SUM(BilledRevenueDetail[BilledQuantity]))
    )

VAR DateInfo =
    SELECTCOLUMNS(
        Date,
        "BilledDate", Date[Date],
        "Year", Date[Year],
        "MonthName", Date[MonthName]
    )

VAR Result =
    NATURALINNERJOIN(
        PayItemProjects,
        BilledPayItem
    )

VAR FinalResult =
    NATURALINNERJOIN(
        Result,
        DateInfo
    )

RETURN
    SELECTCOLUMNS(
        FinalResult,
        "PayItemId", [PayItemId],
        "Project_id", [Project_id],
        "ProjectName", [ProjectName],
        "PayItemNumber", [PayItemNumber],
        "Description", [Description],
        "UnitofMeasureName", [UnitofMeasureName],
        "ForecastTakeOffQuantity", [ForecastTakeOffQuantity],
        "ForecastUnitRevenue", [ForecastUnitRevenue],
        "SumForecastFinalRevenue", [SumForecastFinalRevenue],
        "BilledQuantity", [BilledQuantity],
        "SumBilledRevenue", [SumBilledRevenue],
        "BilledDate", [BilledDate],
        "Year", [Year],
        "MonthName", [MonthName]
    )

[d_gosbell · Accepted solution]

Date is a key word in DAX. If you have a table called Date you need to quote it with single quotes.

eg 'Date'[Date] instead of just Date[Date]