Lets say I have a chart that looks like this:
| Project | Revision | Forecast Finish |
| A | 0 | 3/2/2022 |
| A | 1 | 3/2/2022 |
| A | 2 | 3/2/2022 |
| B | 0 | 5/1/2022 |
| B | 1 | 5/1/2022 |
| C | 0 | 11/30/2022 |
| D | 0 | 12/15/2021 |
| D | 1 | 12/15/2021 |
| D | 2 | 12/15/2021 |
| D | 3 | 12/15/2021 |
| D | 4 | 12/15/2021 |
| D | 5 | 12/15/2021 |
I want to create a bar chart that returns a distinct count of the unique project numbers in the Project Column that have been revised at least once. (the distinct count would be three--Project C has revision number 0, indicating it was never revised)
This is the DAX code I am currently using:
Distinct Count of Projects with at least one revision by Forecast Finish Date =
CALCULATE(
DISTINCTCOUNTNOBLANK('Projects'[Project])
, 'Projects'[Revision] > 0
, USERELATIONSHIP('Date'[Date], 'Projects'[Forecast Finish])
)
The bar chart seems to be correct, but I can't figure out how to get the drill through the way I want it.
If the user wants to drill through and get project details for these revised orders, I don't want them to see every revision. Right now when the user drills through into my bar chart, they see all of the revisions for the revised projects. I worry this will confuse users, and cause them to ask "Why is the distinct count 3 but the drill through has eight rows?":
| Project | Revision | Forecast Finish |
| A | 1 | 3/2/2022 |
| A | 2 | 3/2/2022 |
| B | 1 | 5/1/2022 |
| D | 1 | 12/15/2021 |
| D | 2 | 12/15/2021 |
| D | 3 | 12/15/2021 |
| D | 4 | 12/15/2021 |
| D | 5 | 12/15/2021 |
Instead I would like the drill through to show:
| Project | Revision | Forecast Finish |
| A | 2 | 3/2/2022 |
| B | 1 | 5/1/2022 |
| D | 5 | 12/15/2021 |
If I try removing the revision number and just having the Project number, it still has the same number of rows as the revisions that are greater than 0 like so:
| Project | Forecast Finish |
| A | 3/2/2022 |
| A | 3/2/2022 |
| B | 5/1/2022 |
| D | 12/15/2021 |
| D | 12/15/2021 |
| D | 12/15/2021 |
| D | 12/15/2021 |
| D | 12/15/2021 |
I'm familiar with getting to the highest order revision by writing somthing like this, but then I would lose USERELATIONSHIP(), right?
Count Projects by Highest Revision =
COUNTX(
VALUES('Projects'[Project])
, [Highest Order Revision]
)
([Highest Order Revision] would be MAX('Project'[Revision]). On an unrelated note, I've found that for some reason it only narrows down to the highest project revision per project if I reference the measure. If I write MAX('Project'[Revision]) instead of [Highest Order Revision], it seems to just return the highest order revision on the table of all the orders (so it returns 5, rather than creating a mini-table with 5 revisions on D, 0 revisions on C, 1 revision on B, and 2 revisions on A... Unrelated to my main problem however.)
What do you recommend?
