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.
This is a follow up to this question: Calculate how many leads owned (based on a start/end date) in a dynamic range of dates.
I have a PBIx set up here :
As stated in my earlier question, I need to be able to count unique leads owned in any given time frame.
The measure:
is close to working, but it's not distinct counting when I select a month from the filter. It's very close, May 2020 should be 43 distinct, and any month selected beyond June is returning all rows.
I would also like to use the TIMEFRAMES data set to filter by, using Timeframe and Selected to filter the dates table. But I'm
Solved! Go to Solution.
Here is the measure I ended up using:
Hi,
I think the correct way to solve this is to split the Leads dataset into multiple rows (one for each date). This way we will need only the single date column (we will be able to do away with Start Date and End Date columns).
What's your opinion?
The real dataset is around 500,000 rows, I don't think that's feasible.
I looked at your pbix file. When I go to Data View on your Leads table and put in your filter criteria on the two date columns, I see 42 filtered rows. I think your measure is correct.
Two other comments:
1. Should Leads that have an end date in the selected month be counted? If so, you can change to > MIN instead of >MAX.
2. You can use your Timeframes table w/o making any relationship. Just use two variables to harvest the value of your start and end dates, and then use those variables in place of the MAX(Dates[Date]) in your measure.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you, your 2. solution works for me!
Here is the measure I ended up using:
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |