Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
48 | |
45 | |
19 | |
16 |