Intersect function in Dynamic Matrix

[cheyzaguirre]

Hi, I need help solving a new use case for me (marketing). I need to know how many customers visit the stores physically after browsing the company's web portals. The values should show the visits to the store only of the customers who browsed the portal in the same month, how many visit the store the following month, and so on. I have built part of the solution using the Intersect function, but I am not getting the measure to dynamically consider the start and end of each month based on a month id.

In the following image you can see that 4 customers who visited the web during January 2020. It is also observed in the second table that in the same month there were 9 visits to the stores, the third table is the one that should show how many visited (of the 4 web clients) were made the same month, for this, row 1 represents the same month, row 2 represents the month of February 2020. According to the data, for the column January 2020 and the same month (row 1) there were 5 visits (customers can be repeated), the second row, that is, those who visited the second month (February 2020) should be 2 visits.
Here is the pbix file that I am using to solve this headache Pbix

I was forgetting something, the matriz should show the row and column totals 

Thanks in advance 

[cheyzaguirre]

@AlB  This is awesome!!  never seen a dax formula like this.  We have a lot to learn, thank you very much for your help

[AlB · Accepted solution]

@cheyzaguirre 

Great explanation now 🙂

I haven't looked in detail at the logic for the code for [2 Visitas x Prospectos] but one thing does stand out. Why would you repeat

IF(ISBLANK(MonthEndStore),BLANK(),COUNTROWS(INTERSECT(TableStoreVisitors, TableWebVisitors )))

11 times on the switch, if the result is exactly the same for each of the 11 options??  You can simplify the switch:

2 Visitas x Prospectos V2 = 
VAR IDMonth = SELECTEDVALUE(ID_Month[ID] ) - 1 
VAR MonthWeb = MIN('Calendar'[Date])
VAR MonthStore = MAX('Calendar'[Date])
VAR MonthIniWeb = STARTOFMONTH(DATEADD(FILTER(LASTDATE('Calendar'[Date]), 'Calendar'[Date] = MonthStore ), IDMonth,MONTH))
VAR MonthEndStore = ENDOFMONTH(DATEADD(FILTER(LASTDATE('Calendar'[Date]), 'Calendar'[Date] = MonthStore ), IDMonth,MONTH))
VAR TableWebVisitors = CALCULATETABLE(
                        SUMMARIZE(Data,Data[Client_ID]),
                        Data[Channel] = "Web")
VAR TableStoreVisitors = CALCULATETABLE( 
               SELECTCOLUMNS(
                FILTER(Data,
                Data[Channel] = "Store"),
                "Visitors",Data[Client_ID]),DATESBETWEEN('Calendar'[Date],MonthIniWeb,MonthEndStore))
RETURN
IF (
    SELECTEDVALUE ( ID_Month[ID] ) IN GENERATESERIES ( 1, 11 ),
    IF (ISBLANK ( MonthEndStore ), BLANK (), COUNTROWS ( INTERSECT ( TableStoreVisitors, TableWebVisitors ) ) )
)

Note that this is functionally equivalent to what you had. Now we can create another measure that uses the measure above and that will work at the totals.

2 Visitas x Prospectos V2 TOT = 
    SUMX (
        CROSSJOIN ( DISTINCT ( 'Calendar'[Period] ), DISTINCT ( ID_Month[ID] ) ),
        [2 Visitas x Prospectos V2]
    )

Place this last measure in your matrix visual 3. You might want to change it for the result at the grand total.

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[cheyzaguirre]

Hi @AlB ,
This is the link to the updated pbix Pbix

Yes, the measurement is the one that goes in visual 3 (matrix).
This is the explanation for the case of web visitors from 01-2020, on that date there were 4 web clients, the clients who visited the web in that month were Client_ID 1, 2, 6, 9

In the matrix we must see the first column (01-2020) and row 1 that represents the same month (01-2020), there you can see that that month there were 5 visits to the store, the customers who visited that date (from the 4 web clients) those 5 visits were made by 3 of them (Clien_ID 1, 2, 9).

From the same 4 web clients, only two of them (Clien_ID 6, 9) visited the store in February 2020 (row 2 in the matrix)

And only 3 client (Clien_ID 1, 2, 6) visited the store in March 2020 (row 3 in the matrix)

I hope I have been clearer with this explanation

[AlB]

@cheyzaguirre 

I'm afraid  do not understand what the code you show is. Is it the measure that goes into matrix 3? If so the code looks incomplete (no RETURN statement, etc...)

On top of that, I do not quite understand what the question is. You'll have to try and explain it a bit more.

It'd probably be best if you also attach the file with this latest version

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[cheyzaguirre]

Here's an update. We managed to get the formula to recognize the start and end periods for each month (ID_Mes), it was just a small detail, now we are using Datesbetween. Now it correctly shows us the visits to the store in visual 3, we only need to solve the issue of the row and column totals.

@AlB 

VAR TableStoreVisitors = CALCULATETABLE(

SELECTCOLUMNS(

FILTER(Data,

Data[Channel] = "Store"),

"Visitors",Data[Client_ID]),DATESBETWEEN('Calendar'[Date],MonthIniWeb,MonthEndStore))

[AlB]

Hi @cheyzaguirre 

Why are you using the ID in the rows of matrix visual 3?

This measure will give you the number of people that visited the store AFTER visiting the website. Note that it does not count cases where the web and the store were visited on the same day. You would need time (on top of the date) on those cases. See it all at work in the attached file (Page 2)

Visited store after web =
COUNTROWS (
    FILTER (
        DISTINCT ( Data[Client_ID] ),
        VAR firstPhysicalVisit_ =
            CALCULATE ( MIN ( Data[Date] ), Data[Channel] = "Store" )
        RETURN
            CALCULATE (
                COUNT ( Data[Date] ),
                Data[Channel] = "Web",
                Data[Date] < firstPhysicalVisit_
            ) > 0
    )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[cheyzaguirre]

Hi @AlB ,

The ID field refers to the number of the month in which the customer visits the store, for example, the first column is 01-2020, row 1 represents the same month 01-2020, therefore the customers who visited the web on 01-2020 and were to the store the same month are counted in this row. Row 2 represents the following month, that is, it represents 02-2020, there are the customers who visited the web on 01-2020 but who went to the store in month 02-2020. For the second column 02-2020, row 1 represents the month 02-2020 and row 2 represents the month 03-2020. I hope I have clarified your doubt.
Thank you very much for your interest in helping us, we are reviewing your recommendation to see if we can adapt it to the final solution.