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.
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
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 |
---|---|
46 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
61 | |
50 | |
45 | |
20 | |
17 |