Returning a table and applying specific filter on a column

[rohanjha1988]

Hi All,

I have one table called FrontlineVisit. In this table I have  columns CustomerId and CustomerType. In CustomerType column there are basically 3 paramaters - Ordering Client, Non Ordering, Prospect. 

I am trying a measure - In this I want a table in return which will have all unique CustomerId with their customerType as Prospect. 

For example if the main-table is - 

CustomerID       CustomerType

1                         Prospect

2                         Ordering Client

3                         Prospect

4                         Non ordering 

So the measure should return the following table - 

Customer ID

1

3

I'll apply this measure in a summerization formula like Countrow. 

Pls help!!

[v-yalanwu-msft · Accepted solution]

Hi, @rohanjha1988 ;

You could create a new table as follows:

newtable =
SUMMARIZE (
    FILTER (
        'Table',
        [CustomerType] = "Prospect"
            && CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[CustomerID] ) ) = 1
    ),
    'Table'[CustomerID],
    'Table'[CustomerType])

and the measure is below:

count = 
var _newtable = SUMMARIZE(FILTER('Table',[CustomerType]="Prospect"&&CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[CustomerID]))=1),'Table'[CustomerID],'Table'[CustomerType])
return COUNTROWS(_newtable)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@rohanjha1988 , Create a measure like this and use it with customer id in visual

count 1=  countrows(filter(Table, Table[CustomerType] ="Prospect"))

[rohanjha1988]

Actually I have to calculate month wise numbers of new prospects. Now for this I need the table returning CustomerID only with Prospect filter. Then only I can go ahead and calculate this. 

Is there any way of getting that table?

[amitchandak]

@rohanjha1988 ,

new table = calculatetable(Table, Table[CustomerType] ="Prospect")

Table in a measure

measure =

var _tab = summarize(filter(Table, Table[CustomerType] ="Prospect"), [Customer ID])

return

countrows(filter(Table, Table[Customer ID] in _tab ))

[rohanjha1988]

This formula is working but it is not able to filter on unique customer ID. We need to get the count of unique customer ID.