topic Re: Dax Formula not returning values in DAX Commands and Tips

Dax Formula not returning values

jimmy46 — Wed, 17 Jul 2024 21:30:25 GMT

Hello everyone, I'm still new to PowerBI and trying to get the hang of things.

I have two tables called Opportunity and Sales Rep Promotion dates connected by OwnerId to SFDC ID.

I created a formula column in the Opportunity table that calculates the first date for when each rep closed their first opportunity, which is working properly.

FirstCloseDate = CALCULATE( MIN('Opportunity'[CloseDate]), ALLEXCEPT('Opportunity', 'Opportunity'[OwnerId]) )

Now I'm trying to calculate the difference between each rep's start date, and the "FirstCloseDate". This is the formula I'm using:

DaysToFirstDeal = VAR FirstCloseDate = CALCULATE( MIN('Opportunity'[FirstCloseDate]), FILTER( 'Opportunity', 'Opportunity'[OwnerId] = 'Sales rep start dates'[User ID] ) ) RETURN IF( ISBLANK(FirstCloseDate), BLANK(), DATEDIFF('Sales rep start dates'[Start date], FirstCloseDate, DAY) )

The formula doesn't return any errors. However, it's not showing any value and I can't figure out why.

What could be the cause of this issue? Thanks for the help!

Re: Dax Formula not returning values

Anonymous — Thu, 18 Jul 2024 05:40:44 GMT

Hi jimmy46

Could you please provide some sample data and the expected results based on the sample data? That would be very helpful. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

At present, I can only give you some suggestions based on your formula for your reference.

Since your previous formula, which works fine, has already calculated the earliest closing opportunity time for each OwnerID, and from your description it seems that there is a relationship between the two tables, I think the second formula may not need to calculate FirstCloseDate again. You can try to use the first formula directly in the second formula.

Best Regards,
Yulia Xu

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

Re: Dax Formula not returning values

jimmy46 — Thu, 18 Jul 2024 17:29:00 GMT

Hi Yulia, here's some screenshots of the data:

It's some simple Short date data.

Re: Dax Formula not returning values

Anonymous — Fri, 02 Aug 2024 08:38:16 GMT

Hi jimmy46

I would like to apologize for the belated reply.

Below is my test.

sample data:

relationship:

I used the formula you gave directly, and the result is as follows

I can't reproduce your problem, could you please provide more details?

Best Regards,
Yulia Xu

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