Forum Discussion

Gazi_Sohan's avatar
Gazi_Sohan
Icon for Helper I rankHelper I
2 years ago

DAX measure using calculate

Hi guys, I am trying to calculate Net Income between 2 accounts (account numbers between 400000 and 957090), however I want the account number to come from the columns instead of hard coded. I tried it and got the following exception.
How can I fix this ?

 

 

Here is the DAX measure that I am currently using

Net_income =
CALCULATE(
[Actual Amounts],
// COADetails_New[AccountID] >= "400000" && COADetails_New[AccountID] <= "957090"
COADetails_New[AccountID] >= 'Balance Sheet Table'[Start Account] && COADetails_New[AccountID] <= 'Balance Sheet Table'[End Account]
)

 

Need Help
Tutorial Requests

4 Replies

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Gazi_Sohan 

    What is the relationship you have between two tables, please share image for Data model
    COADetails_New and 'Balance Sheet Table'

    Is Account No, Start Account and End Account are number datatype or Text Datatype

    Please reply

     

    • Gazi_Sohan's avatar
      Gazi_Sohan
      Icon for Helper I rankHelper I
      2 years ago

      Hey PijushRoy 
      Please see my recent reply on this post. You will find answers to all of your questions there

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Gazi_Sohan,

    Measure formula does not support to directly use other table columns across multiple tables.

    I'l like to suggest you use aggregate functions to handle table field value to aggregate them and return get single value as condition in these expressions:

    Net_income =
CALCULATE (
    [Actual Amounts],
    FILTER (
        ALLSELECTED ( COADetails_New ),
        COADetails_New[AccountID] >= MIN ( 'Balance Sheet Table'[Start Account] )
            && COADetails_New[AccountID] <= MAX ( 'Balance Sheet Table'[End Account] )
    )
)

    Regards,

    Xiaoxin sheng

  • Gazi_Sohan's avatar
    Gazi_Sohan
    Icon for Helper I rankHelper I
    2 years ago

    Hi Anonymous 
    Sorry for the late reply as I was too busy with loads of projects in my company.

     

    There are some corrections that the company has made. So now, instead of the Balance Sheet table, we are using COADetails_New table which is connected to the Fact table directly with a "One to Many" relationship as shown below in the data model - 

     


    Also, we have got the DAX measure working but still it has got a hard-coded portion in it in as a filter
    // COADetails_New[AccountID] >= "400000" && COADetails_New[AccountID] <= "957090")

    See the screenshot below - 
     

     

    We don't want this portion to be hard-coded.
    Instead we want to read it from the table itself i.e. COADetails_New table.

    In the COADetails_New table, here are a snap of the main columns that can be taken into account to perform the required operation -
    https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-Help-with-Calculate/m-p/3631102?lightbox-message-images-3631102=1023329i254D678F6871AC30#M140446 

    In our current DAX measure we have hard-coded the AccountID for a range between 400000 and 957090 (as mentioned in my first post). Also we don't have the columns Start Account and End Account in COADetails_New table like we had before in the Balance Sheet table (I mentioned in my first post).

    Now, how can I put the range dynamically instead of hard-coding ? Remember, you can't use the totalling column from COADetails_New table and break it into Start and End account because it remains outdated, the client never updates this.