Forum Discussion

vnqtrang's avatar
vnqtrang
Helper I
4 years ago
Solved

Distinctcount except Filtered values

Hi,   I would like to use distinct count which excludes the values in 2 filters  Here are the 2 filters :  Filter(all(table), LEFT(Table[ColumnName],1)="R")  Filter(all(table), LEFT(Table[Column...
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    vnqtrang , Try like

    Measure = distinctCOUNT(Table[ColumnName]) -

    calculate(distinctCOUNT(Table[ColumnName]), Filter((table), LEFT(Table[ColumnName],1)="R" || LEFT(Table[ColumnName],2)="CR") )

     

     

    Measure =

    calculate(distinctCOUNT(Table[ColumnName]), Filter((table), not( LEFT(Table[ColumnName],1)="R" || LEFT(Table[ColumnName],2)="CR"))  )

     

    or

     

    Measure =

    calculate(COUNT(Table[ColumnName]), Filter((table), not( LEFT(Table[ColumnName],1)="R" || LEFT(Table[ColumnName],2)="CR"))  )

amitchandak's avatar
amitchandak
Super User
4 years ago

vnqtrang , Try like

Measure = distinctCOUNT(Table[ColumnName]) -

calculate(distinctCOUNT(Table[ColumnName]), Filter((table), LEFT(Table[ColumnName],1)="R" || LEFT(Table[ColumnName],2)="CR") )

 

 

Measure =

calculate(distinctCOUNT(Table[ColumnName]), Filter((table), not( LEFT(Table[ColumnName],1)="R" || LEFT(Table[ColumnName],2)="CR"))  )

 

or

 

Measure =

calculate(COUNT(Table[ColumnName]), Filter((table), not( LEFT(Table[ColumnName],1)="R" || LEFT(Table[ColumnName],2)="CR"))  )

vnqtrang's avatar
vnqtrang
Helper I
4 years ago

Thank you !