Hello everyone. This is my first post so I hope it goes well. I’ve included two screenshots of some mock tables I created in Excel to provide context. I have one master table called Financials which has all of the financial transactions associated with a claim number. I have another table called Test which is built from the Financials table.
The Test table contains the distinct values of claim numbers since in the Financials table a claim number can have multiple transactions. In the event that a claim number has multiple transactions, it would be on separate rows, as shown in the screenshot. Below is the formula I used to create my Test table with distinct claim numbers.
Test Table = distinct(Financials[Claim Number])
My Request
In reference to the screenshot for the attached Test table, I would like to create two columns titled Investigation and Settlement. In plain English, I would like the DAX formula to retrieve the check amount or even better, sum the check amount, if the payment type in the Financials table is Investigation. The same logic would apply when payment type is Settlement.
I’m thinking something like: if lookupvalue from payment type in Financials = 'investigation' then sum check amount. The Test table will then have one row for each claim and it will have summed up all of the investigation payments and all of the settlement payments.
I hope I didn’t make this too confusing and thank you for helping. I’m sure it’s simple yet I can’t figure it out!
This would be easier if you’d provided a small sample PBIX but seems to me you can solve this with a few Measures. One just a SUM of the Financials[Check Amount] and the other measures branch of using CALCULATE where Financials[Payment Type] = “Investigation” or “Settlement”
Avoid calculated columns there is no need to store this data in your model increasing the file size.
By the way, why do you need a Test Table with unique claim numbers?
You could try to solve this in the Power Query Editor. In your Financials Table you could pivot the column ‘Payment Type’ and choose ‘Check Amount’ as the Values Column. That would generate the Test Table like the one you provided in your example. The column ‘Investigation’ and the column ‘Settlement’ will show subtotals then. I hope this will help.
@uriah1977 is on the right track. This is a data modeling issue and should be handled in PQ, since that is what it’s made to do. DAX can solve this, but the more complex your DAX has to be is a sure sign that your data model could be the issue. Plus complex DAX is slow, prone to errors, harder to write and troubleshoot. If you fix the problem in the ETL stage you will find the DAX much easier
I was able to find one solution which is using the following code. What are your thoughts on this solution? The solution gave me what I want but I’m not sure if I can solve this using the Power Query editor?
