Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
flyinggnugget
Frequent Visitor

Distinct count while ignoring filter context

Hi All,

 

Would appreciate some help with some power bi DAX. I have the following 3 tables

Plans:

PlanPlan TypePlan Type Id
A1A1
A2A2
B1B1
B2B2
B3B3
C1C1
C2C2
C3C3
C4C4

 

Plan Type:

Plan Type IdScoresAttribute Id
A1561
A1342
A2781
A2232
B1841
B1722
B2931
B2842
B3371
B3522
C1841
C1912
C2481
C2842
C3171
C3452
C4381
C4742

 

Attributes:

Attribute IdAttribute
1Attribute 1
2Attribute 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:

AttributeScoresDistinct Count of Plan Type IdDistinct Count of All Plan Type Id
Attribute 15612
Attribute 17812
Attribute 22312
Attribute 23412

 

Thanks!

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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

 

lbendlin_0-1714566848511.png

 

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

 

lbendlin_1-1714567079792.png

 

View solution in original post

v-xuxinyi-msft
Community Support
Community Support

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”.

vxuxinyimsft_3-1714634767807.png

 

If you want to use measure, you can try the following steps.

 

Change the cross-filter direction of the relationship to Both

vxuxinyimsft_0-1714634585738.png

 

vxuxinyimsft_1-1714634664334.png

 

 

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:

vxuxinyimsft_2-1714634702732.png

 

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.

View solution in original post

2 REPLIES 2
v-xuxinyi-msft
Community Support
Community Support

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”.

vxuxinyimsft_3-1714634767807.png

 

If you want to use measure, you can try the following steps.

 

Change the cross-filter direction of the relationship to Both

vxuxinyimsft_0-1714634585738.png

 

vxuxinyimsft_1-1714634664334.png

 

 

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:

vxuxinyimsft_2-1714634702732.png

 

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
Super User
Super User

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

 

lbendlin_0-1714566848511.png

 

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

 

lbendlin_1-1714567079792.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.