March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
29 | |
24 | |
20 | |
16 |