Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How to count distinct with relationships?

Hello,

 

I have these relationships in my data model, everything is connected using the In-links -> ID

 

 

I would like to count distinct the values in SWT_Module (ID) that are linked to c_CustomerID in the first box and display in a Pivot Table in Excel.

Something like the following:

  • c_CustomerID 3
    • ID 4
    • ID 9

 

count distinct = 2

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Follow the arrows in your data model. You can't do your count distinct with your current configuration - these two tables cannot talk to each other.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Why? All the tables are connected.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    that is necessary but not sufficient. They need to be connected the right way.  Think of a street analogy with one way streets and two way streets etc.  Can you travel from your starting table all the way to the destination table?  Generally you cannot travel from one dimension table to another dimension table in a star schema unless you sacrifice search filter directions by making one of the connections bidirectional.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous 
    Try: 

     

    calculate(distinctcount('SWT_Module'[ID]), filter('SWT_Module', 'SWT_Module'[ID] in values('STR_A_Sample'[c_CustomerID])))

     


    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.