Other stores that customers have visited after visiting a certain store

[tzake]

Hey guys,

First of all, I want to say that I'm new to everything BI. Including Power BI.

I was asked to do a Power BI em

bedded integration which went well and now I'm being asked to help with some reports.

Most of the reports were simple and straightforward and I'm amazed of what Power BI can do, but I'm not a BI guy...

The report I need help with is described in the title. I believe it's a variation of the basket analysis pattern (like products bought together, just it's stores visited together) but I can't figure out the correct DAX expression.

I've cross joined the merchants (stores) so I could use it in a slicer and display as a Sankey / Pie Chart.

I need to calculate the Measure as the number of customer (users) that have visited both the source merchant/store and the destination merchant / store.

I don't know if this is the right approach, although the joined table seems to help with filtering.

Schema: https://www.screencast.com/t/k4kRmyHk31Zv

Report: https://www.screencast.com/t/RSmIxA7NMEue

.PBIX file: https://1drv.ms/u/s!ApFGSFVCrdePgdoQetpe3ymP_kjbAg

[v-yuezhe-msft]

@tzake,

Could you please post expected result based on the sample data in your PBIX file? I am not very clear about the measure values you want to get for each merchant/store.


Regards,
Lydia

[tzake]

Hey,

I will use the following notations for users, zones and merchants: Ui, Zi, Mi where i is the id/index.

So the sample data basically says that:

M1 was visited by U1 (via Z1) and by U2 (via Z2).

M2 was visited by U1 (via Z3) and by U2 (via Z4).

M3 was only visited by U1 (via Z5).

M4 was only visited by U1 (via Z7).

For each merchant pair (from the cross joined table), I would like to count the (distinct) users that have visited both.

That is the measure I'm looking for.

Expected result:

Case A: Slice by M1

M1 M2 2 (both U1 and U2 visited M1 and M2) 

M1 M3 1 (just U1 visited M1 and M3)

M1 M4 1 (just U1 visited M1 and M4)

Case B: Slice by M2

M2 M1 2 (both U1 and U2 visited M1 and M2) 

M2 M3 1 (just U1 visited M2 and M3)

M2 M4 1 (just U1 visited M2 and M4)

Case C: Slice by M3

M3 M1 1 (just U1 visited M3 and M1)

M3 M2 1 (just U1 visited M3 and M2)

M3 M4 1 (just U1 visited M3 and M4)

Case 😧 Slice by M4

M4 M1 1 (just U1 visited M4 and M1)

M4 M2 1 (just U1 visited M4 and M2)

M4 M3 1 (just U1 visited M4 and M3)

I don't know if this is the best sample data, if you want I could add more relevant visits.

Another case (not on the data) If for example U1 hadn't visited M4, the measure would be 0 and should be filtered out from the chart.

[tzake]

@v-yuezhe-msft,

I've updated the sample a little bit: PBIX

Following this topic i have managed to calculate the visitors of all selected merchants on the left.

The question is, how can i get that measure in the right side of the page.

That is for each merchant pair to calculate the same measure.

Thanks,

Mihai

[tzake]

So, I used the following measure:

Visitors of All Selected Merchants = COUNTX(
  FILTER(
    SUMMARIZE(Visits, Visits[UserId], "MerchantsVisited", DISTINCTCOUNT(Merchants[Id])),
    [MerchantsVisited]=COUNTROWS(VALUES(Merchants[Id]))
  ), [MerchantsVisited]
)

And then used a calculated column in the cross joined table:

Column = CALCULATE([Visitors of All Selected Merchants], FILTER(ALL(Merchants), Merchants[Name]=MerchantsCrossJoin[MerchantDestination] || Merchants[Name] = MerchantsCrossJoin[MerchantSource]))

This yields the right results.

But, being a calculated column it does not react to slicers.

Making it a measure would not let me reference the source and destination merchants in the filter expression.

Is there any way around this?

[tzake · Accepted solution]

Ok,

So I decided to take another route, the classic basket analisys way with a filter table and it seems to work.

https://1drv.ms/u/s!ApFGSFVCrdePgdoptyuQW9-DqrHwOA

Inspiration from here: https://community.powerbi.com/t5/Desktop/DAX-Function-for-Customers-also-bought/td-p/147454

Now, any ideas how i could make just one pie chart with drill down?

Because joining with the merchants in the zone table seems to make the query cross filter the results and i only get the selected merchant. Whereas I would like them all.