DAX Calculated Column in the Table View

[TJK]

I want to create a calculated column in the Table View in Power BI. I want the calculated column to group the "Company" column based on how many unique values occur in the "Lender" column.

For example, the below table has 3 companies, and each company has two records of their lenders. Company1 has two different lenders, so I want the calculated column to list "2" for both of its records. Company2 and Company3 have the same lender listed twice, so I want the calculated column to list "1" for both of its records.

Company,	Lender,	Count of Lenders
Company1,	Private,	2
Company1,	Public,	2
Company2,	Private,	1
Company2,	Private,	1
Company3,	Public,	1
Company3,	Public,	1

I know this is possible to achieve with Power Query, but can I achieve it in the Table View once the data is loaded out?

[Greg_Deckler · Accepted solution]

@TJK Try this:

Column =
  VAR __Company = [Company]
  VAR __Table = DISTINCT( SELECTCOLUMNS( FILTER( 'Table', [Company] = __Company ), "Lender", [Lender] ) )
  VAR __Result = COUNTROWS( __Table )
RETURN
  __Result

[Greg_Deckler · Accepted solution]

@TJK Try this:

Column =
  VAR __Company = [Company]
  VAR __Table = DISTINCT( SELECTCOLUMNS( FILTER( 'Table', [Company] = __Company ), "Lender", [Lender] ) )
  VAR __Result = COUNTROWS( __Table )
RETURN
  __Result

[TJK · Accepted solution]

Thanks Greg! I was able to solve it with the following too:

Column = 

CALCULATE(DISTINCTCOUNT('Table'[Lender]),ALLEXCEPT('Table','Table'[Company]))