Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
But the other condition is that there are some clients that only have "Cancelled" transactions, this is shown in the Status column.
Solved! Go to 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.
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.
Thank you! That did it!!!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |