Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
20 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
22 |