Forum Discussion

danb's avatar
danb
Resolver I
1 year ago
Solved

DAX - Crossjoin with Criteria

I have been asking Copilot but without success so coming to the real experts!  I have two tables, one called 'BrandForecast' and the other 'Running Calendar'. I am trying to figure out how to do a...
Need Help
  • Daniel29195's avatar
    Daniel29195
    1 year ago

    danb 

    create a calculatedtable as below : 

    filter ( 

    crossjoin ( table1 , table2 ) , 

     [Week Starting (Monday Start) >= [Promotion Start Date]  && [Week Starting (Monday Start)  <= [Promotion End Date]

    )

     

    hope this helps . 

lbendlin's avatar
lbendlin
Super User
1 year ago

There is no magic to it.

 

Result = FILTER(CROSSJOIN(BrandForecast,'Running Calendar'),[Week Starting] IN CALENDAR([Promotion Start Date],[Promotion End Date]))

 

No idea if there is any performance benefit using GENERATE instead. Probably not.

 

Result2 = GENERATE(BrandForecast,filter('Running Calendar',[Week Starting] in CALENDAR([Promotion Start Date],[Promotion End Date])))
danb's avatar
danb
Resolver I
1 year ago

Thanks - I haven't played with GENERATE before but will check that out.