Forum Discussion

Grant_Reid's avatar
Grant_Reid
Frequent Visitor
5 years ago
Solved

Using output of INTERSECT

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.

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

1 Reply

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    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.