Forum Discussion

CAPSTALKER's avatar
CAPSTALKER
Icon for Helper I rankHelper I
7 years ago
Solved

DAX Formula to retrieve a Field value based on another tables date value


Hi,

I'm pretty new to DAX. I need to retrieve the most latest value of a column from one of our tables.
I also need to filter some values. I've tried the following expression.

NetSales= 
CALCULATE(
    SUM(sales[Sales]) - SUM(sales[TurnOver]);
    FILTER(
        nimike; (item[ItemType] in {"S";"K"}) || (item[Group] = "M25") && Date[Date] = Today()
    )
)

 

The error I got:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

What am I doing wrong. Any help would be highly appreciated!

Need Help
Show and Tell
  • MattAllington's avatar
    MattAllington
    7 years ago

    You are filtering a table called “nimike” but the filters you are trying to apply are from a table called “item” and also the date table. You can’t do this. 

     

    I can’t tell what is the correct formula, but I can tell you what the correct syntax is. You will have to test it. 

     

    NetSales =
CALCULATE (
    SUM ( sales[Sales] ) - SUM ( sales[TurnOver] );
    FILTER ( Item; item[ItemType] IN { "S"; "K" } || item[Group] = "M25" );
    FILTER ( 'date'; Date[Date] = TODAY () )
)

1 Reply

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    7 years ago

    You are filtering a table called “nimike” but the filters you are trying to apply are from a table called “item” and also the date table. You can’t do this. 

     

    I can’t tell what is the correct formula, but I can tell you what the correct syntax is. You will have to test it. 

     

    NetSales =
CALCULATE (
    SUM ( sales[Sales] ) - SUM ( sales[TurnOver] );
    FILTER ( Item; item[ItemType] IN { "S"; "K" } || item[Group] = "M25" );
    FILTER ( 'date'; Date[Date] = TODAY () )
)