Using output of INTERSECT

[Grant_Reid]

Hi

From my request for help below, clearly I'm someone new to DAX. I have a measure in which I use INTERSECT to return a list of CustomerKeys of Customers who have been Invoiced in the last 30 days - see below.

30 Returning Name =
VAR InterestPeriod =
SUMMARIZE (
FILTER ( Transactions, Transactions[DaysSinceInvoice] <= 30 ),
Transactions[CustomerKey]
)
VAR Balance =
SUMMARIZE (
FILTER ( Transactions, Transactions[DaysSinceInvoice] > 30 ),
Transactions[CustomerKey]
)
VAR CustomerKeys =
INTERSECT ( Balance, InterestPeriod )
RETURN
CustomerKeys

I need to know how I can use this in a Table / Matrix to return the relevant Customer name from the "Customers" table. There is a 1 to many relationship defined between the "Customers" and "Transactions" table on CustomerKeys column.

[v-yingjl · Accepted solution]

Hi @Grant_Reid ,

INTERSECT() function would return a whole table so it could not be used in a measure directly.

You can create a measure like this, put it in the visual filter and set its value as 1:

visual control = 
VAR InterestPeriod =
    SUMMARIZE (
        FILTER ( Transactions, Transactions[DaysSinceInvoice] <= 30 ),
        Transactions[CustomerKey]
    )
VAR Balance =
    SUMMARIZE (
        FILTER ( Transactions, Transactions[DaysSinceInvoice] > 30 ),
        Transactions[CustomerKey]
    )
VAR CustomerKeys =
    INTERSECT ( Balance, InterestPeriod )
RETURN
    IF (
        MAXX (
            FILTER ( CustomerKeys, [CustomerKey] IN DISTINCT ( 'Customers'[CustomerKey] ) ),
            [CustomerKey]
        )
            = SELECTEDVALUE ( Customers[CustomerKey] ),
        1,
        0
    )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.