Forum Discussion
DAX Calculated Column in the Table View
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?
TJK Try this:
Column = VAR __Company = [Company] VAR __Table = DISTINCT( SELECTCOLUMNS( FILTER( 'Table', [Company] = __Company ), "Lender", [Lender] ) ) VAR __Result = COUNTROWS( __Table ) RETURN __ResultThanks Greg! I was able to solve it with the following too:
Column =CALCULATE(DISTINCTCOUNT('Table'[Lender]),ALLEXCEPT('Table','Table'[Company]))
2 Replies
TJK Try this:
Column = VAR __Company = [Company] VAR __Table = DISTINCT( SELECTCOLUMNS( FILTER( 'Table', [Company] = __Company ), "Lender", [Lender] ) ) VAR __Result = COUNTROWS( __Table ) RETURN __ResultThanks Greg! I was able to solve it with the following too:
Column =CALCULATE(DISTINCTCOUNT('Table'[Lender]),ALLEXCEPT('Table','Table'[Company]))