Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
Hi @setis
what would you like to do with those 2 values? Add them up?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries