How to ignore columns in table visual when summing values across dataset.

[SPa]

I have two table with the following relevant fields:

- CS

* Name

* Related Case

- SC

* Child Case Ref

An example table would be

Name  Related Case  Child Case Ref

Sean    Case1             Case1a

Sean    Case 2            Case2a

Sean    Case 3            Case3a

David   Case 4            Case4a

What I want is to add a column that would show the number of distinct 'Related Case' values per name.

So for Sean that would be 3 and david 1.

Name Related Case  Child Case Ref   NEW COLUMN

Sean    Case1             Case1a              3

Sean    Case 2            Case2a              3

Sean    Case 3            Case3a              3

David   Case 4            Case4a              1

How can I do this?

[bhanu_gautam]

You can see in attached PBIX file that it is giving correct answer 

Just replace column and table name 

[SPa]

Ah ok, thank you for explaining. My issue is that I have a directquery connection to the data. Therefore a new column with calculate is not possible. 

[Anonymous · Accepted solution]

Hi @SPa 

Thanks for the reply from @Greg_Deckler and @bhanu_gautam .

@SPa , you can try the following measure.

Measure = COUNTX(FILTER(ALL('Table'), [Name] = MAX([Name])), [Related Case])

Output:

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.

[bhanu_gautam]

@SPa, You can achieve this using DAX and since you have not mentioned relation between table 1 and 2 , I have not considered it and it won't impact the outcome

Please find the attached PBIX

DAXDistinctRelatedCasesCount =

CALCULATE(

    DISTINCTCOUNT('CS'[Related Case]),

    ALLEXCEPT('CS', 'CS'[Name])

)

[SPa]

When I try this, I get the following result:

Name Related Case  Child Case Ref   NEW COLUMN

Sean    Case1             Case1a              1

Sean    Case 2            Case2a              1

Sean    Case 3            Case3a              1

David   Case 4            Case4a              1

[Greg_Deckler]

@SPa Try:

Measure = 
  VAR __Name = MAX( 'Table'[Name] )
  VAR __Table = FILTER( ALL( 'Table' ), [Name] = __Name )
  VAR __Result = COUNTROWS( 'Table' )
RETURN
  __Result