topic Re: DAX Calculated Column in the Table View in DAX Commands and Tips

DAX Calculated Column in the Table View

TJK — Wed, 28 Aug 2024 13:21:53 GMT

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?

Re: DAX Calculated Column in the Table View

Greg_Deckler — Wed, 28 Aug 2024 13:32:57 GMT

TJK Try this:

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

Re: DAX Calculated Column in the Table View

TJK — Wed, 28 Aug 2024 14:24:48 GMT

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

Column =

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