Forum Discussion

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[ColumnName],2)="CR")

ColumnName

R0000ER23

R0000ER25

R0000ER22

CR099DF12

CR099CV12

CR099ER34

CR099ER40

WP000022

WP000023

WP000023

The return result should be 2 (WP000022, WP000023).

Thank you in advance.

Tg

Tips and Tricks

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")) )

4 Replies

amitchandak
Super User
4 years ago
vnqtrang , Create this measure and plot it with columnname

calculate(distinctCOUNT(Table[ColumnName]), Filter((table), LEFT(Table[ColumnName],1)="R" || LEFT(Table[ColumnName],2)="CR") )
- vnqtrang
  Helper I
  4 years ago
  This one shows the filtered values. I would like to count the other values except these 2 filtered values. Hope I clear enough.
  - 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")) )