Forum Discussion
Distinct count while ignoring filter context
Hi All,
Would appreciate some help with some power bi DAX. I have the following 3 tables
Plans:
| Plan | Plan Type | Plan Type Id |
| A | 1 | A1 |
| A | 2 | A2 |
| B | 1 | B1 |
| B | 2 | B2 |
| B | 3 | B3 |
| C | 1 | C1 |
| C | 2 | C2 |
| C | 3 | C3 |
| C | 4 | C4 |
Plan Type:
| Plan Type Id | Scores | Attribute Id |
| A1 | 56 | 1 |
| A1 | 34 | 2 |
| A2 | 78 | 1 |
| A2 | 23 | 2 |
| B1 | 84 | 1 |
| B1 | 72 | 2 |
| B2 | 93 | 1 |
| B2 | 84 | 2 |
| B3 | 37 | 1 |
| B3 | 52 | 2 |
| C1 | 84 | 1 |
| C1 | 91 | 2 |
| C2 | 48 | 1 |
| C2 | 84 | 2 |
| C3 | 17 | 1 |
| C3 | 45 | 2 |
| C4 | 38 | 1 |
| C4 | 74 | 2 |
Attributes:
| Attribute Id | Attribute |
| 1 | Attribute 1 |
| 2 | Attribute 2 |
In the model view I have Plans table joined to Plan Type table on Plan Type Id column. Plan Type table joined to Attributes table on Attribute Id column.
In my report view I have a slicer on Plan to select either A, B or C.
I have a table visual showing the Attribute and Score. I am trying to create measure to show the distinct count of Plan Type Id for that particular attribute and score as well as the distinct count of Plan Type Id under that Plan, example when A is selected in the slicer as shown below:
| Attribute | Scores | Distinct Count of Plan Type Id | Distinct Count of All Plan Type Id |
| Attribute 1 | 56 | 1 | 2 |
| Attribute 1 | 78 | 1 | 2 |
| Attribute 2 | 23 | 1 | 2 |
| Attribute 2 | 34 | 1 | 2 |
Thanks!
Your "Plans" table is actually a "Plan Type IDs" table.
Then you can use the table visual to show all the required data.
I’d like to acknowledge the valuable input provided by lbendlin . His idea was instrumental in guiding my approach. Please allow me to make some additions.
You can add the Plan Type Id in the Plans table to get the “Distinct Count of All Plan Type Id”.
If you want to use measure, you can try the following steps.
Change the cross-filter direction of the relationship to Both
Distinct Count of Plan Type Id = CALCULATE( DISTINCTCOUNT('Plan Type'[Plan Type Id]), FILTER( ALL('Plan Type'), RELATED(Plans[Plan Type Id]) = SELECTEDVALUE(Plans[Plan Type Id]) ))Distinct Count of All Plan Type Id = CALCULATE( DISTINCTCOUNT('Plan Type'[Plan Type Id]), FILTER( ALL('Plan Type'), RELATED(Plans[Plan]) = SELECTEDVALUE(Plans[Plan]) ))Result:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Your "Plans" table is actually a "Plan Type IDs" table.
Then you can use the table visual to show all the required data.
I’d like to acknowledge the valuable input provided by lbendlin . His idea was instrumental in guiding my approach. Please allow me to make some additions.
You can add the Plan Type Id in the Plans table to get the “Distinct Count of All Plan Type Id”.
If you want to use measure, you can try the following steps.
Change the cross-filter direction of the relationship to Both
Distinct Count of Plan Type Id = CALCULATE( DISTINCTCOUNT('Plan Type'[Plan Type Id]), FILTER( ALL('Plan Type'), RELATED(Plans[Plan Type Id]) = SELECTEDVALUE(Plans[Plan Type Id]) ))Distinct Count of All Plan Type Id = CALCULATE( DISTINCTCOUNT('Plan Type'[Plan Type Id]), FILTER( ALL('Plan Type'), RELATED(Plans[Plan]) = SELECTEDVALUE(Plans[Plan]) ))Result:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.