Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello - I need your help in creating a measure or calculated column
I have attached the sample data file. Click Here
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.
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.
Thanks
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |