Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Solved! Go to Solution.
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 Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
108 | |
102 | |
94 | |
71 |
User | Count |
---|---|
173 | |
134 | |
132 | |
102 | |
95 |