Need help with Calculated Column or Measure

[gauravnarchal]

Hello - I need your help in creating a measure or calculated column

1. Show all invoice numbers and total Amounts in columns against each (Settlement) Auth. When there are multiple invoice numbers against the same AUTH is to to be separated with a slash “/”.

I have attached the sample data file. Click Here

[v-chenwuz-msft]

Hi @gauravnarchal 

The link to you pbix file has expired. So I create some data depend on your discripation.i put my pbix file in the end and you can reference.

Try this measure:

Measure =
CONCATENATEX( 'ARInvoices', [invoice number], "/" )

If you want to create a column the following formula will helpful.

Amount =
SUMX(
    FILTER( 'CashReceipt', [AUTH] = EARLIER( 'settlement'[AUTH] ) ),
    CashReceipt[Amount]
)
invoicenumber =
CONCATENATEX(
    FILTER( ARInvoices, ARInvoices[AUTH] = EARLIER( settlement[AUTH] ) ),
    [invoice number],
    "/"
)

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gauravnarchal]

Hi @v-chenwuz-msft  - Thank you for looking into this and helping me. I have attached my PBIX file for you to look at the data.

Click Here

Thanks

[gauravnarchal]

Hi @v-chenwuz-msft  - I have tried your solution, but I am not able to get the desired results for Invoice numbers.

I have attached my data again for you to see. Click Here

Many thanks for your help in advance.

[v-chenwuz-msft]

Hi @gauravnarchal .Your model has some problems if you want to do this ,you should edit them like the following make sure these tables can filter by Settlement table’s AUTH field.

And this measure,but unselect the blank checkbox in the filter

Measure =
CONCATENATEX( 'ARInvoices', [invoice number], "/" )

 Pbix in the end.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @gauravnarchal, 

please consider remodeling your Power BI dataset. The concept of the model can be called Header/Detail style. The most recent article from the people of sqlbi.com is exactly mentioning this style/concept, and why it should be avoided: Header/Detail vs Star Schema models in Tabular and Power BI - SQLBI

Next to that, here you will find an introduction to data modeling in regards of Power BI data models: Model data in Power BI - Learn | Microsoft Docs

The many-to-many relationship will make the required measure (using the DAX function CONCATENATEX to create a string from the Invoice Numbers) expensive as you are traversing the relationships in the "wrong" direction.

Hopefully, this provides some new insights that will help to tackle your challenge.

Regards,

Tom