Forum Discussion

flyinggnugget's avatar
flyinggnugget
Frequent Visitor
2 years ago
Solved

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...
  • lbendlin's avatar
    2 years ago

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

     

     

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

     

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.