topic Re: Need formula help in DAX Commands and Tips

Need formula help

yaya1974 — Wed, 31 Jul 2024 21:55:51 GMT

Hello, I have 2 tables with absolutely nothing to create a relationship. But I need data from the first table into the second table, filtered on data from the second table. I have this formula but it is not returning correct value. Can someone please help?

Adj = IF(ABC[Customer]="A",CALCULATE(FIRSTNONBLANK(Commodities[Adj],1),ALLEXCEPT(ABC,ABC[Model],ABC[Finish],ABC[PL Market])))

I've even tried using multiple filters and still same result. I do not want to use a measure or powerquery, he needs to be a new column.

it is returning (0.218) for all rows. I am expecting Jan-Jun 0.0181 and Jul-Dec 0.0242

Thank you!

Re: Need formula help

adudani — Wed, 31 Jul 2024 22:58:34 GMT

Hello,

Kindly provide the sample input/output in a usable format (excel, csv, table etc.) masking sensitive information.

reference : https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M607150

Thanks,

Re: Need formula help

Anonymous — Mon, 05 Aug 2024 09:37:41 GMT

Hi, yaya1974

I am glad to help you.

Since there are no relationships between the tables, the formula in ALLEXCEPT in the DAX formula you provided does not achieve the result you are expecting.

Could you please provide some examples of text format data in the tables (excluding sensitive data), along with your expected results, calculation logic and special examples. Simplified pbix files would be great.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Need formula help

yaya1974 — Mon, 05 Aug 2024 13:48:29 GMT

Hello, thank you for responding. Here is a screen shot of what I am looking for. Unfortately I get blocked when trying to share a document. Hope this helps. My date columns are not "dates" they are text too btw.

Thank you for any help!

Lori

Re: Need formula help

sjoerdvn — Mon, 05 Aug 2024 14:05:57 GMT

If there are no relationships between the tables, you should be using LOOKUPVALUE or TREATAS.

Re: Need formula help

yaya1974 — Mon, 05 Aug 2024 14:17:44 GMT

ok. I did sorta get it to work. using lookupvalue. However, the issue is from Table 1. The adj column is not filling in all the way, so I first need to get issue in table 1 fixed. here is my dax code for adj column in table 1. can anyone help fix this?

Thank you!

Re: Need formula help

sjoerdvn — Mon, 05 Aug 2024 15:39:39 GMT

mmm, didn't you say that your date columns where actually text type? If that is the case than things like MIN, MAX and EARLIER are not going to work if the date format is mmm-yy.
So you would need to create a proper date column first, and although you could try that in DAX, I would advice to do that in Power Query or even in the source if that's possible.

Re: Need formula help

yaya1974 — Mon, 05 Aug 2024 15:43:26 GMT

Correct they were text, but I did already switch to date, to get the lookupvalue to work.

Biggest issues is getting all rows to fill in. Thanks!

Re: Need formula help

Anonymous — Wed, 14 Aug 2024 06:02:51 GMT

Hi, yaya1974

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Need formula help

yaya1974 — Wed, 14 Aug 2024 13:43:04 GMT

Hello, No it is not resolved, still looking how to get the rows filled in. Unfortunately I am not able to provide files, due to my company blocking anyone outside of company. All I have is the screen shot above.

Do you have a suggestion on how I can share? Sorry, I do appreciate everyone's help though!!

Thank you!

Lori

Re: Need formula help

sjoerdvn — Wed, 14 Aug 2024 15:28:25 GMT

So for that first table, you want a computed column, where the result is the difference between the current value and the most recent value that was different ?

But does that table also have a column that indicates the customer? and the values should be determined by customer?

Re: Need formula help

yaya1974 — Wed, 14 Aug 2024 16:08:41 GMT

My formula works, I am getting the correct output, BUT I need it to fill in the rest of the rows. I have color coded the rows that need filled in with the same number. My code just needs to be corrected somehow to fill in, I just can't figure that out.

Thank you!