Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey All,
Basically, I have two tables: Case, and opportunity. These are linked through another table, Customer.
Basically, I wanted to find out how many cases there are within one year previous of the opportunities close date, for each opportunity. Using a column is too much memory because of the size of the objects, so I wanted to use a measure.
This is how I calculated it, but it doesn't seem to be working:
@Anonymous , Try like
Cases Yr before Close = Calculate(DistinctCount('SF Case'[id]), Filter(all('SF Case'[id]), ('SF Case'[createddate]) > MIN('SF Opportunity Dataset'[Prev year close]) && ('SF Case'[createddate]) < MAX('SF opportunity Dataset'[Closedate])),values('SF Case'[id]))
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Says I can't attach the powerbi file, so I'll screenshot:
Link between the two tables through the "account id" - an account can have many opps and many cases, but each account id is distinct, which allows the link
Opportunity dataset - basically want to count the SF cases created between those two dates. These are linked through the account object field "accountid"
case data, in which I want to count the ids (which represent case) where the date is between the two dates on the opportunity object
The output would ultimately be this;
where each opportunity ID is listed, and the count of linked cases between the two dates on the opportunity table is calculated. I'd probably put this on a scatter plot used to compare against other variables
Thanks
Measure still doesn't "see" created dates here without the aggregate. I'll create something to use and publish it here
@Anonymous , Not very clear, Try like
Cases Yr before Close = Calculate(DistinctCount('SF Case'[id]), Filter(values('SF Case'[id]), ('SF Case'[createddate]) > MIN('SF Opportunity Dataset'[Prev year close]) && ('SF Case'[createddate]) < MAX('SF opportunity Dataset'[Closedate])),all('SF Case'[id]))
Very similar to Current Employee - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
To clarify, I'm looking to build a scatter plot that shows each opportunity, with the x axis being # of cases (based on their created date) being within 1 year previous of the opportunities close date. The Y axis is based on a metric on the account object, so not relevant here.
Hey! Thanks for the response. Because I'm using a measure, the case created date isn't working because its looking for an aggregate
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |