Forum Discussion

TJK's avatar
TJK
Helper I
2 years ago
Solved

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 ex...
calculated column
DAX
groupby
Table View
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    TJK Try this:

    Column =
  VAR __Company = [Company]
  VAR __Table = DISTINCT( SELECTCOLUMNS( FILTER( 'Table', [Company] = __Company ), "Lender", [Lender] ) )
  VAR __Result = COUNTROWS( __Table )
RETURN
  __Result
  • TJK's avatar
    TJK
    2 years ago

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

    Column = 

    CALCULATE(DISTINCTCOUNT('Table'[Lender]),ALLEXCEPT('Table','Table'[Company]))
Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

TJK Try this:

Column =
  VAR __Company = [Company]
  VAR __Table = DISTINCT( SELECTCOLUMNS( FILTER( 'Table', [Company] = __Company ), "Lender", [Lender] ) )
  VAR __Result = COUNTROWS( __Table )
RETURN
  __Result
TJK's avatar
TJK
Helper I
2 years ago

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

Column = 

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