Forum Discussion

Reddyp's avatar
Reddyp
Helper I
4 years ago
Solved

DAX query_Need help

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
ProductDatecost_amount_physicalCost_amount_postedStore CostLocation 
Cycle########$10 $10 $0
Bike######## $22$22Auckland $22

 

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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]
)

3 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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's avatar
      Reddyp
      Helper I
      4 years ago

      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's avatar
        Samarth_18
        Community Champion
        4 years ago

        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]
)