Forum Discussion
Anonymous
5 years agoNot applicable
NATURALINNERJOIN slow by context filter
I have some salesperson spread in a lot of cities, based on selected salesperson value I have to show all their "competitors" in the cities which a salesperson selected sell. I'm using the natural join, but is extremilly slow to go further with this solution, is there some alternative to naturaljoin in powerbi?
EVALUATE
VAR threemonthsago =
EOMONTH ( TODAY (), -3 ) + 1
VAR t_day =
TODAY ()
VAR salesperson =
SELECTEDVALUE ( 'competitors_sales'[sales_id] )
//CITIES WHERE SALESMEN SELLS
VAR city_person =
SELECTCOLUMNS (
SUMMARIZE (
FILTER (
'fact_sell',
'fact_sell'[person_id] = salesperson
),
'dim_salesperson'[person_id]
),
"City", [city_id] + 0
)
//CITIES WHERE THEIR COMPETITORS SELL AS WELL
VAR Allcities =
SELECTCOLUMNS (
SUMMARIZE (
'fact_sell',
'dim_salesperson'[city_id],
"qtt_sales",
CALCULATE (
DISTINCTCOUNT ( 'fact_sell'[person_id] ),
'dim_calender'[dat] > threemonthsago
&& 'dim_calender'[dat] <= t_day
)
),
"City", [city_id] + 0,
"qty", [qtt_sales]
)
VAR all2gether =
NATURALINNERJOIN ( city_person, Allcities )
RETURN
SUMX ( all2gether, qty )
No Replies