Select venues with repeating customers based on another venue selection - DAX needed!

[Anonymous]

Hello folks,

I have a calculation for the repeating customers per venue (restaurant) that goes like this:

Repeat Customers =

COUNTROWS(

FILTER(

VALUES( Resy[AccountID] ),

CALCULATE( COUNTROWS( Resy ) ) > 1

)

)

Now I need to have a table that would show the repeating customer counts per venue when you select another venue in a table or in a slicer. So if you have a guest that visited A restaurant twice or more and that customer visited venue B and G I want to show that. Really stumbled upon that.

Thanks,

Stan

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Repeat Customers = 
var t = 
    SUMMARIZE(
        ALLSELECTED('Table'),
        'Table'[AccountID],
        'Table'[Restaurant],
        "Count",
        COUNTROWS('Table')
    )
var tab = 
SUMMARIZE(
    'Table',
    'Table'[AccountID],
    "Flag",
    var _count = 
    COUNTROWS(
        FILTER(
            t,
            [AccountID]=EARLIER('Table'[AccountID])&&
            [Count]>=2
        )
    )
    return 
    IF(
        _count>0,
        1,0
    )
)
var _result = 
COUNTROWS(
    FILTER(
        tab,
        [Flag]=1
    )
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Allan,

You have a great solution that I can use in my other visual if I want to see the actual accounts.

But my goal is to show all the other venues the repeating customer visited.

Like in the data example you created, if you select venue A, that Accounts 1, 2, 3 and 4 visited, the venues B, C, F, D, E should show with counts. 

Thank you,

Stan

[Anonymous]

Just to be more clear on the final result. I should have a Venue slicer with all restaurants. If I pick Venue A, I should be able to see the following:

Venue	Count
B	2
C	3
D	2
F	1

[v-alq-msft]

Hi, @Anonymous 

I'm sorry for the late reply. You may try to create a calculated table and a measure as below. The pbix file is attached in the end.

Calculated table:

Restaurant = DISTINCT('Table'[Restaurant])

Measure:

Result = 
var _restaurant = SELECTEDVALUE('Table'[Restaurant])
var tab = 
SUMMARIZE(
    ALL('Table'),
    'Table'[AccountID],
    "Count",
    IF(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                [AccountID]=EARLIER('Table'[AccountID])&&
                [Restaurant]=_restaurant
            )
        )>0,
        1,0
    )
)
return
IF(
    NOT(SELECTEDVALUE('Table'[Restaurant]) in DISTINCT(Restaurant[Restaurant])),
    SUMX(
        tab,
        [Count]
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Allan, thank you for your reply! I checked the pbix and played with the selections. Seems like it works for some seletions and doesn't work for others. For instance, baced on the data if I select restaurant F (only AccountID = 3 visited that venue), I am expected to see A - 1, B - 0, D -1 but I see the following:

I will try to think on your code but if you have any idea please post it.

Thank you,

Stan

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Please try the following measure. The pbix file is attached in the end.

Result = 
var _restaurant = SELECTEDVALUE('Table'[Restaurant])
var tab = 
SUMMARIZE(
    FILTER(
        ALL('Table'),
        NOT('Table'[Restaurant] in DISTINCT(Restaurant[Restaurant]))
    ),
    'Table'[AccountID],
    "Flag",
    IF(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                [AccountID]=EARLIER('Table'[AccountID])&&
                [Restaurant] in DISTINCT(Restaurant[Restaurant])
            )
        )>0,
        1,0
    )
)
var newtab =
CALCULATETABLE(
    DISTINCT('Table'[AccountID]),
    FILTER(
        tab,
        [Flag]=1
    )
)
return
IF(
    NOT(SELECTEDVALUE('Table'[Restaurant]) in DISTINCT(Restaurant[Restaurant])),
    COUNTROWS(
        FILTER(
            ALL('Table'),
            [AccountID] in newtab&&
            [Restaurant]=SELECTEDVALUE('Table'[Restaurant])
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Allan, that did the trick! Thank you so much!!!

[Greg_Deckler]

@Anonymous - It's difficult to say other than you will have to capture your current values for the customer and restaurant and then change your filter context to pick up the other restaurants. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.