Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rainynights
Helper II
Helper II

Making a filtered measure to show past customers who haven't made a purchase this month, via tooltip

So the use-case is this:

 

Let's say I want to know instantly who my drop-offs are. I have a starting table like this, tracking purchase behaviors of 6 unique customers, by the month that they made any purchase:

 

0a2d53c16435b3c3b58e458d820bafeb

 

From the above table, we then derive the following visual, and add along a very basic tooltip that shows you who the customers were that made a purchase in that month:

 

4b51c674b8d5aa094889a0d0859c725f

 

Above, we simply see the distinct count of customers per month listed, and when I mouseover I see who they were. 

 

Now - I want to add 1 twist to this. Instead of the tooltip showing me the customers from that month, I want it to show me the customers from all the previous months, who did not make a purchase in that month.

 

E.g. if I moused over September, I would see the names of Mary and Jane, as these are past customers who did not make a purchase in September. It's tough to figure out even where to begin trying this!

 

Here's the sample PBIX file used to generate the above 2 screenshots:
https://1drv.ms/u/s!AqOYqK3B8ETjb0c6OL3DhtRbm94?e=CQuoCJ

1 ACCEPTED SOLUTION
rajulshah
Super User
Super User

Hello @rainynights

 

Please use the measure as below in the tooltip table:

 

 

Customers =
VAR SelectedMonth =
    SELECTEDVALUE ( 'Table'[Start of Month] )
VAR CustomersPresent =
    CALCULATETABLE (
        VALUES ( 'Table'[Customer] ),
        FILTER (
            ALL ( 'Table'[Start of Month] ),
            'Table'[Start of Month] = SelectedMonth
        )
    )
RETURN
    CALCULATE (
        CONCATENATEX (
            VALUES ( 'Table'[Customer] ),
            'Table'[Customer],
            UNICHAR ( 10 )
        ),
        FILTER (
            ALL (
                'Table'[Start of Month],
                'Table'[Customer]
            ),
            'Table'[Start of Month] <> SelectedMonth
                && NOT ( 'Table'[Customer]
                IN CustomersPresent )
        )
    )

 

 

Hope this helps. If it doesn't, please let me know.

View solution in original post

3 REPLIES 3
rajulshah
Super User
Super User

Hello @rainynights

 

Please use the measure as below in the tooltip table:

 

 

Customers =
VAR SelectedMonth =
    SELECTEDVALUE ( 'Table'[Start of Month] )
VAR CustomersPresent =
    CALCULATETABLE (
        VALUES ( 'Table'[Customer] ),
        FILTER (
            ALL ( 'Table'[Start of Month] ),
            'Table'[Start of Month] = SelectedMonth
        )
    )
RETURN
    CALCULATE (
        CONCATENATEX (
            VALUES ( 'Table'[Customer] ),
            'Table'[Customer],
            UNICHAR ( 10 )
        ),
        FILTER (
            ALL (
                'Table'[Start of Month],
                'Table'[Customer]
            ),
            'Table'[Start of Month] <> SelectedMonth
                && NOT ( 'Table'[Customer]
                IN CustomersPresent )
        )
    )

 

 

Hope this helps. If it doesn't, please let me know.

@rajulshah 

Now that I think about expanding this feature further:

Is there any way to get the information to show in the form of a table, as opposed to a concatenated string with line breaks?

For instance, in case I also want to include the date of last purchase of that customer in a tooltip! Seems like a very interesting use-case!

Hi @rajulshah ,

 

Thanks a lot for the help!

 

One correction I made to that was I changed 

'Table'[Start of Month] <> SelectedMonth 
to:
'Table'[Start of Month] < SelectedMonth
 
So on mouseover, it excludes future customers! But that is perfect for my needs. I have never used the CONCATENATEX function before, so that was interesting to take a look at too!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.