Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |