Need help with Calculated Column or Measure

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

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.