Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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