DAX query_Need help

[Reddyp]

I have DAX syntax for  -- >>   if date = '2021' then use cost_amount_physical otherwise use Cost_amount_posted

Column =
IF (
    YEAR ( 'Table (2)'[Date] ) = 2021
        && 'Table (2)'[cost_amount_physical] <> BLANK (),
    'Table (2)'[cost_amount_physical],
    'Table (2)'[Cost_amount_posted]
)

need to add one more thing for the above query, "filter by Location which is on other sheet
if date = (Sheet 1)'2021' then use (Sheet1)cost_amount_physical otherwise use (Sheet1)Cost_amount_posted and filter by (Sheet2)location = "Acukland"

						Desired Result
Product	Date	cost_amount_physical	Cost_amount_posted	Store Cost	Location
Cycle	########	$10		$10		$0
Bike	########		$22	$22	Auckland	$22

[Samarth_18]

HI @Reddyp ,

You could try below code:-

Column =
IF (
    YEAR ( 'sheet (1)'[Date] ) = 2021
        && 'sheet (1)'[cost_amount_physical] <> BLANK ()
        && RELATED ( 'sheet(2)'[location] ) = "Auckland",
    'sheet (1)'[cost_amount_physical],
    'sheet (1)'[Cost_amount_posted]
)

Note:- I am considering you have relationship between both the sheets.

BR,

Samarth

[Reddyp]

@Samarth_18 thank you, but this is not working 

Error: The syntax for '&&' is incorrect. (DAX(IF( YEAR('Inventory Invent_Trans_Fact'[Date_Financial]) = 1990 && 'Inventory Invent_Trans_Fact'[Cost_Amount_Physical] <> BLANK(), && RELATED('Inventory Invent_Location_Dim'[Invent_Location_Id]) = 'W0400', 'Inventory Invent_Trans_Fact'[Cost_Amount_Physical], 'Inventory Invent_Trans_Fact'[Cost_Amount_Posted]))).

column =

IF(

YEAR('Inventory Invent_Trans_Fact'[Date_Financial]) = 1990

&& 'Inventory Invent_Trans_Fact'[Cost_Amount_Physical] <> BLANK(),

&& RELATED('Inventory Invent_Location_Dim'[Invent_Location_Id]) = 'W0400',

'Inventory Invent_Trans_Fact'[Cost_Amount_Physical],

'Inventory Invent_Trans_Fact'[Cost_Amount_Posted])

and relation is active

[Samarth_18 · Accepted solution]

@Reddyp Kindly update your code as below:-

column =
IF (
    YEAR ( 'Inventory Invent_Trans_Fact'[Date_Financial] ) = 1990
        && 'Inventory Invent_Trans_Fact'[Cost_Amount_Physical] <> BLANK ()
        && RELATED ( 'Inventory Invent_Location_Dim'[Invent_Location_Id] ) = "W0400",
    'Inventory Invent_Trans_Fact'[Cost_Amount_Physical],
    'Inventory Invent_Trans_Fact'[Cost_Amount_Posted]
)