Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
43 | |
21 | |
21 | |
14 |
User | Count |
---|---|
124 | |
42 | |
39 | |
28 | |
24 |