Intersect function in Dynamic Matrix

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...

DAX

Intersect

AlB

5 years ago

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