Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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

Need Help
Show and Tell
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    4 years ago

    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)

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        lbendlin
        Super User
        4 years ago

        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.