DAX Calculated column: One value per unique ID from another column

[wudanna]

Hi all,

My first time posting on the forums, so I'm hoping I have formatted my question properly. I have a two-part problem with DAX:

1. I'm building a budget report with multiple lines for budget per name (listed as ID numbers). I need to calculate the sum of all budgets, filtered by unique project. However, I want to sum only one line of budget for each name, as multiple rows are from different invoices that draw from the same budget.

2. I would like to ideally assign one project description to a budget. Would this be a calculated column with empty cells associated with duplicate budget rows? As an additional complication, I am using DirectQuery from SQL Server, so certain functions like FIND do not work in this framework.

Below is an example table:

My goal would be to created either calculated measures and/or columns appended to the table:

I've attempted to apply a WINDOW function nested within SUMX for the first problem, but that did not return the correct total budget.

budgetUpdate = SUMX(

   WINDOW(

      1, ABS, -1, ABS,

      SUMMARIZE(ALLSELECTED(project), project[name], project[budget]), , 

      PARTITIONBY(project[name])

    ),  

    CALCULATE(MAX(project[budget])

)

)

Please let me know if I can provide any more context/information. Thanks for the help in advance!

[wudanna]

Thank you for the advice @Anonymous,.

This may be a little off topic from the PBI, but since there is already a view with partition by for the client IDs (or "name" in my example), would it be easier to customize the index based on the "name" field? Sorry for the lack of clarity - sometimes the client "description" isn't consistent across the same project.

Thank you again,

Diana

[Anonymous · Accepted solution]

HI @wudanna,

If you are working with direct query mode which limited the calculated column/table usages, I'd like to suggest you directly add two custom fields in the data connector SQL statements. (extract the client part from the description field and add index based on the client group) Then you can summary the records remarked with index value 1 of each client groups.

Regards,

Xiaoxin Sheng