Trying to filter a dax table by a column created with a measure

[moesteez]

Hi,

Im trying to filter a table that has been created with addcolumns but am finding it suprisingly difficult. I have a column in the table that is created by using a measure, but how do I now filter by the results of this measure? For example I have the following dax:

ADDCOLUMNS(
        VALUES( 'Invoice'[ContactID] ),
        "NewCustomerDate", [NewCustomers],
        "Number of Orders", [OrderCount] )

But now I only want to show the results where the ordercount is equal to 1. I have tried the following but keep getting an error:

VAR CustomersWithNewDate =

ADDCOLUMNS(
        VALUES( 'Invoice'[ContactID] ),
        "NewCustomerDate", [NewCustomers],
        "Number of Orders", [OrderCount] )

VAR minunique = 

CALCULATETABLE(countrows(CustomersWithNewDate), [OrderCount] = 1 ) 

Does anyone know how I can get this to work?

Thanks in advance

[TomMartens · Accepted solution]

Hey @moesteez, 

I have to admit that I do not fully understand your requirements, especially this
" I only want to show the results where the ordercount is equal to 1."
is not clear.
What do you mean with "show?"

Nevertheless after you have created the virtual table you can use this to filter it

var filteredvirtualtable = 
FILTER(
    CustomersWithNewDate
    , [Number of Orders] = 1
)

I do not understand the part 
var minunique = 
because CALCULATETABLE returns a table, but COUNTROWS creates a scalar values.

Hopefully, my example of how to filter a virtual table helps to tackle your challenge.

Regards,

Tom