Getting Wrecked By USEDRELATIONSHIP

DAX DAX Calculations
1

Hi everyone,

Hope you’ve got a nice day coming up in NZ. So I have a fun problem and sadly I haven’t cracked stripping my model of private data so I hope screen shots will help instead of sharing the PBIX.

I am trying to work with USERELATIONSHIP and Salesforce data. We have a checkbox in Salesforce that signifies a salesperson has sent a proposal and it has a second field where they input the date they sent the proposal. Salesforce imported these as True/Fales and Date/Time but I have converted them to 1/0 and Date in the query editior. For the majority of my reports I use Opportunity Close Date but in this case I would like to calculate the number of proposals sent by their respective proposal sent date…

Here is my DAX:

Number of Proposals = 
   SUM(Opportunity[Proposal_Delivered__c])

and

Number of Proposals Using ProposalSentDate =
CALCULATE( SUM('Opportunity'[Proposal_Delivered__c]),
USERELATIONSHIP(Dates[Date], Opportunity[Date_Proposal_Delivered__c]))

I have also tried using the Number of Proposals Measure in the above formula instead of SUM and it returns the same incorrect values.

image
image.png906×417 20.7 KB

image
image.png1005×477 24.2 KB

As you can see, it is not properly summing the values and they are not properly associated to my date table. Here is some sample data:

image

Here is my model

image

Any thoughts on what I am doing wrong here?

Thank you!

2

Solved my problem. I believe it was a filter context problem! Regardless I can now look at Proposals sent and Total sales in one Matrix with a date column but in the backgrond they are using different date references. The close date for the sales and the proposal delivered date for the proposals. So I can now model, for a given date, the total amount sold and the number of proposals sent. Awesome!

image
image.png1009×223 26.9 KB

3

Ok great