Calculated column and FIRSTNONBLANK

[setis]

Dear all,

I have a calulated column in a table to bring a value from another table that have not relationship in the data model with each other. 

I am in my Sales Invoice Line and the calculated column is:

Amount w VAT2 = 
CALCULATE(
    FIRSTNONBLANK('Job Ledger Entry'[Price LCY (inc VAT)];1);
    FILTER(Job Ledger Entry';
    'Job Ledger Entry'[Document No_] = 'Sales Invoice Line'[Document No_] &&
    'Sales Invoice Line'[Identifier] = 'Job Ledger Entry'[Identifier]))

My problem is that I'm facing a case that has 2 lines in Job Ledger Entry. One with the amount in + and the other in -. 

Both lines have the same Document No_ and Identifier. 

I would line to bring both values. Is it possible?

Thank you in advance

[v-shex-msft · Accepted solution]

HI @setis,

You can try to use CONCATENATEX function to combine all matched records:

Amount w VAT2 =
CALCULATE (
    CONCATENATEX (
        VALUES ( 'Job Ledger Entry'[Price LCY (inc VAT)] );
        [Price LCY (inc VAT)];
        ";"
    );
    FILTER (
        'Job Ledger Entry';
        [Price LCY (inc VAT)] <> BLANK ()
            && [Document No_] = EARLIER ( 'Sales Invoice Line'[Document No_] )
            && [Identifier] = EARLIER ( 'Sales Invoice Line'[Identifier] )
    )
)

Regards,
Xiaoxin Sheng

[amitchandak]

You can use sumx. It will sum. As new column

 new Column = sumx(FILTER('Job Ledger Entry';
    'Job Ledger Entry'[Document No_] = 'Sales Invoice Line'[Document No_] &&
    'Sales Invoice Line'[Identifier] = 'Job Ledger Entry'[Identifier]),'Job Ledger Entry'[Price LCY (inc VAT)])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[ImkeF]

Hi @setis  

what would you like to do with those 2 values? Add them up?