Distinct count while ignoring filter context

[flyinggnugget]

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!

[lbendlin · Accepted solution]

Your "Plans"  table is actually a "Plan Type IDs"  table.

Then you can use the table visual to show all the required data.

[v-xuxinyi-msft · Accepted solution]

Hi @flyinggnugget 

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.

[lbendlin · Accepted solution]

Your "Plans"  table is actually a "Plan Type IDs"  table.

Then you can use the table visual to show all the required data.