Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to create a measure (I don't have access to the dataset) to get a distinct count of status, by grouping year and ID. I also only want to count statuses selected by the user. I need to know for a given year and ID if there is more than one status, but be able to ignore some of those statuses when a user deselects them from the page filter.
I have tried several formulas and they all come close, but don't work all the way.
In this table the distinct count is what I am expecting when all statuses are selected:
Year | Solution | Investment ID | Investment Name | Investment Status | Project/OpEx | Labor/Non | Amount Planned | Distinct Count |
2020 | S1 solution | 25 | Investment 1 | Approved | Project | Non | 10 | 2 |
2020 | S1 solution | 25 | Investment 2 | Draft | Project | Non | 10 | 2 |
2021 | S1 solution | 25 | Investment 2 | Draft | Project | Non | 15 | 1 |
2022 | S1 solution | 25 | investment 2 | Draft | Project | Non | 15 | 1 |
2020 | S2 solution | 26 | Investment 3 | Approved | Project | Non | 10 | 1 |
2020 | S2 solution | 27 | investment 4 | Approved | Project | Non | 5 | 2 |
2020 | S2 solution | 27 | investment 4 | Ready for division review | Project | Non | 8 | 2 |
2020 | S2 solution | 28 | investment 5 | Approved | Project | Non | 10 | 1 |
2021 | S2 solution | 26 | investment 3 | Approved | Project | Non | 12 | 2 |
2021 | S2 solution | 26 | investment 3 | Ready for ICSC review | Project | Non | 15 | 2 |
2021 | S2 solution | 27 | investment 4 | Ready for division review | Project | Non | 10 | 1 |
2021 | S2 solution | 28 | investment 5 | Approved | Project | Non | 10 | 1 |
2020 | S3 solution | 30 | investment 6 | Approved | Project | Non | 10 | 1 |
2021 | S3 solution | 30 | investment 6 | Approved | Project | Non | 10 | 1 |
This is what I would want to see if the user has chosen to only select Approved and Draft statuses:
Year | Solution | Investment ID | Investment Name | Investment Status | Project/OpEx | Labor/Non | Amount Planned | Distinct Count |
2020 | S1 solution | 25 | Investment 1 | Approved | Project | Non | 10 | 2 |
2020 | S1 solution | 25 | Investment 2 | Draft | Project | Non | 10 | 2 |
2021 | S1 solution | 25 | Investment 2 | Draft | Project | Non | 15 | 1 |
2022 | S1 solution | 25 | investment 2 | Draft | Project | Non | 15 | 1 |
2020 | S2 solution | 26 | Investment 3 | Approved | Project | Non | 10 | 1 |
2020 | S2 solution | 27 | investment 4 | Approved | Project | Non | 5 | 1 |
2020 | S2 solution | 27 | investment 4 | Ready for division review | Project | Non | 8 | |
2020 | S2 solution | 28 | investment 5 | Approved | Project | Non | 10 | 1 |
2021 | S2 solution | 26 | investment 3 | Approved | Project | Non | 12 | 1 |
2021 | S2 solution | 26 | investment 3 | Ready for ICSC review | Project | Non | 15 | |
2021 | S2 solution | 27 | investment 4 | Ready for division review | Project | Non | 10 | |
2021 | S2 solution | 28 | investment 5 | Approved | Project | Non | 10 | 1 |
2020 | S3 solution | 30 | investment 6 | Approved | Project | Non | 10 | 1 |
2021 | S3 solution | 30 | investment 6 | Approved | Project | Non | 10 | 1 |
This seems to work but I haven't tested it much. It would be annoying to add any extra columns to the all function. It would be more robust if you could specify the year and ID as the level of grouping.