Forum Discussion

ashmitp869's avatar
ashmitp869
Icon for Responsive Resident rankResponsive Resident
2 years ago
Solved

Help needed with dax query

Hi All,

 

Help me out with the below dax query issue

I have a code 

EVALUATE
VAR __SelectedValue = 509
VAR PayItemProjects =
FILTER(
    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])
		
    ),
    PayItem[PayItemId] = 509)
// RETURN PayItemProjects



VAR BilledPayItem =
FILTER(
 SELECTCOLUMNS(
    SUMMARIZE(
        BilledRevenueDetail,
     	BilledRevenueDetail[PayItemId],
        BilledRevenueDetail[BilledDate],
        "SumBilledRevenue", SUM(BilledRevenueDetail[BilledRevenue]),
        "BilledQuantity", SUM(BilledRevenueDetail[BilledQuantity])
    ),
	 "PayItem[PayItemId]",LOOKUPVALUE(PayItem[PayItemId], PayItem[PayItemId], BilledRevenueDetail[PayItemId]),
	
	BilledRevenueDetail[BilledDate],
	[SumBilledRevenue],
    [BilledQuantity]
 )
 
   , PayItem[PayItemId] = 509)
   
  VAR Result =
    NATURALLEFTOUTERJOIN(
        PayItemProjects,
        BilledPayItem
    ) 
  Return Result	 

 

But getting error

 

 

I have check the data types they are whole number. But still unable to solve.

 

When doing join in Data Model , all my output looks good.

Here is the result in Power Bi table view

 

 

  • HI, ashmitp869 

    In Naturalleftouterjoin  Columns being joined must have the same data type and the same name in both tables.

    here you take 9 column in Payitemprojects and 4 column from Billedpayitem.

    Take same numbers of column from both tables with same name and same data type.

    Refer Link  for more information.

     

    Best Regards

    Dangar.



1 Reply

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar

    HI, ashmitp869 

    In Naturalleftouterjoin  Columns being joined must have the same data type and the same name in both tables.

    here you take 9 column in Payitemprojects and 4 column from Billedpayitem.

    Take same numbers of column from both tables with same name and same data type.

    Refer Link  for more information.

     

    Best Regards

    Dangar.