Why does my Cross Join /Join Filter not work properly?

[Anonymous]

Hello, 

I trying to get the dates between the Start Date and the End Date. So I found a way to work on that, but it is not working the way I want. Somehow it is eliminating most of the events. My original table has 33 events but the calculated table only has 13 events. 

I think the issue is with the cross join filter, and try to change the logic, but did not work. 

Can anybody please tell me how to fix this or is there any better way I can do this? Ultimately I want all events including events that have multiple days. 

Code - 

ProDetail_CrossJoin = 
VAR temp =
    SELECTCOLUMNS(
            ProServ,
                "EventID", ProServ[EventID],
                "StartDate", ProServ[Start],
                "EndDate", ProServ[End]
    )

VAR vCalendar =
    CALENDAR(MINX(temp, [StartDate]), MAXX(temp, [EndDate]))

RETURN
    SELECTCOLUMNS(
        FILTER( CROSSJOIN(temp, vCalendar),
                [StartDate] <= [Date]
                    && [EndDate] >= [Date]
            ),
            "EventID", [EventID],
            "StartDate", [StartDate],
            "EndDate", [EndDate],
            "Detail", [Date]

    )

Sample File - https://drive.google.com/file/d/1qFOk0J4unwwQGsrtT5LJfIZfq98TtnwY/view?usp=sharing

 Thank you so much

[lbendlin]

What are you trying to measure? The number of days you were occupied with each topic? The min and max dates you were occupied with each topic? 

[Anonymous]

@lbendlin 

Thank you for taking the time to review my post, all I want is to get the data between the Start and End dates for the subject and make it one date column.  See the highlighted date range, 

but on my sample file, I am missing so many subjects. I want to be my final result like above.

Any help on this will be highly appricted. Again, thank you 

[lbendlin]

I see now.  Your first issue is that you create a generic calendar rather than event based calendars.

ProDetail_CrossJoin = 
VAR temp = ADDCOLUMNS(ProServ,"Cal",CALENDAR([Start],[End])
...

The second issue is that there is no UNIONX function in DAX.

Which means your approach makes more sense, but is also extremely costly.  I think a different approach will be to get a distinct list of event names, crossjoin that with the overall calendar, and then apply the filters.

VAR cross = CROSSJOIN(
                      SUMMARIZE(ProServ,ProServ[EventID],ProServ[Event Name]),
                      CALENDAR(minx(ProServ,ProServ[Start]),maxx(ProServ,ProServ[End]))
)

Next step is to mark the active days for each event id - across all sub events.

[lbendlin · Accepted solution]

Attached is a working version.

ProDetail_CrossJoin = 
VAR cross = CROSSJOIN(
                      SUMMARIZE(ProServ,ProServ[EventID],ProServ[Event Name]),
                      CALENDAR(minx(ProServ,ProServ[Start]),maxx(ProServ,ProServ[End]))
)
var ename = ADDCOLUMNS(cross,"en",ProServ[Event Name],"dt",[Date])
var test = ADDCOLUMNS(ename,"Include",
                            var l=SELECTCOLUMNS(filter(ProServ,ProServ[Event Name]=[en]),"Incl",
                                      CONTAINS(CALENDAR(ProServ[Start],ProServ[End]),[Date],[dt]))
                            return sumx(l,if([Incl],1,0))
)
return filter(test,[Include]>0)

[Anonymous]

@lbendlin 

​You Sir are Awesome!​ I have been combing through online for one and half weeks to find a solution and I tested out your solution, It's working great. Thank you so much for your time and help. I truly appreciated your help.  

[lbendlin]

Glad to hear it works for you. Be careful with the cardinality - it may perform poorly for larger date ranges and bigger number of events.

I finally submitted an idea for the UNIONX function - thank you for pushing me over the edge.

Microsoft Idea  · UNIONX (powerbi.com)

[Anonymous]

Thanks. I don't think it is going to be large data set, but I will keep my eye on it. Again, thank you so much for the help.