Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I have been investigating on the forum but still have some issues.
I have a table like this:
Id | Date | Value |
1 | 1/1/2020 | Prospect |
1 | 2/2/2020 | Lead |
1 | 5/5/2020 | Opportunity |
2 | 3/3/2020 | Prospect |
2 | 4/4/2020 | Lead |
2 | 7/7/2020 | Opportunity |
I need a measure that shows the last value by id filtering by month. For example, if I select march/2020 I need to have something like:
Id | value |
1 | Lead |
2 | Prospect |
Then, I need to display a chart that shows the number of distinct id´s per value filtering by month
If I select 5/5/2020, I need to see something like:
Value Count of id
Prospect 0
Lead 1
Opportunity 1
This means, we count the number of id´s per value, depending on the last value of an id at a certain date
Thank you @wdx223_Daniel !
It only works for the dates where it has NonBlank values.
How would you display on a chart like this one the number of distinct Id´s based on their last value, filtering by Month?
Create a Dates table for your range, and connect it to your fact table
Then create a measure
and lastly put everything in a visual of your choice. I omitted the year check from the solution - you can add that yourself.
If you want a more elegant (but also costlier) measure you can use
Thank you very much @lbendlin , it works.
How can I display this measure on a chart like this one?:
I want to replicate your measure on this chart so that I can see the number of distinct Id´s based on their last value, filtering by Month.
I don't understand your request. Please show the expected outcome.
Hi @lbendlin
Assuming the first data table of the post.
If I select 5/5/2020 for instance, I need to see something like:
Value Count of id
Prospect 0
Lead 1
Opportunity 1
This means, we count the number of id´s per value, depending on the last value of an id at a certain date
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
9 | |
9 | |
6 | |
5 |
User | Count |
---|---|
18 | |
15 | |
10 | |
9 | |
8 |