Forum Discussion
Intersect function in Dynamic Matrix
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
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
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.
