DISTINCT excluding blanks and "Cancelled" transactions

[Anonymous]

So I have my dataset with Client ID, Date, Status, Amount and a lot of extra columns.

I wanted to segment the clients using an RFM method, so I created a new Table and used the DISTINCT formula to extract all the distinct client IDs but I'm having a problem with 2 conditions. The first one is that there are some clients that don't have an ID so I want them excluded from my table, so I used this formula to successfully exclude the blanks: 

Data RF= (distinct(filter(values('VTEX 2018-2019'[Client ID]);LEN('VTEX 2018-2019'[Client ID])>0)))

But the other condition is that there are some clients that only have "Cancelled" transactions, this is shown in the Status column.

My question is how can I create a Measure that only brings to my new table the customer IDs that are not blanks and that are not "Cancelled"

Thank you all

William

[V-pazhen-msft · Accepted solution]

Hi,  

You can add CALCULATETABLE to your formula, it can be something like the following:

Data RF =

    DISTINCT (

        CALCULATETABLE (

            FILTER ( VALUES ( 'VTEX 2018-2019'[Client ID] ), LEN ( 'VTEX 2018-2019'[Client ID] ) > 0),

            FILTER ( 'VTEX 2018-2019', [Status] <> "Cancelled" ),

            'VTEX 2018-2019'[Customer ID] <> BLANK () ) )

Best, 
Paul

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you! That did it!!!