Forum Discussion

P_R's avatar
P_R
Frequent Visitor
7 years ago
Solved

SQL to DAX: Get SalesPerson Region on a specific date into a measure

I have been working in SQL and SSRS for several years now but i am new to PowerBI. I am having difficulty creating a measure. I have below tables: 1. Order 2. SalesPersonTeam In Order table i have...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi P_R

    Create relationships as below, reference here

     

    Create measures in SalesPersonSalesTeam table

    Start = MAX([StartDate])

end = IF(MAX([ExpirationDate])=BLANK(),DATE(2999,12,31),MAX([ExpirationDate]))

Measure = CALCULATE(SUM('Order'[amount]),FILTER('Order',[datestamp]>=[Start]&&[datestamp]<=[end]))

     

    Best Regards

    Maggie

     

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi P_R

Create relationships as below, reference here

 

Create measures in SalesPersonSalesTeam table

Start = MAX([StartDate])

end = IF(MAX([ExpirationDate])=BLANK(),DATE(2999,12,31),MAX([ExpirationDate]))

Measure = CALCULATE(SUM('Order'[amount]),FILTER('Order',[datestamp]>=[Start]&&[datestamp]<=[end]))

 

Best Regards

Maggie

 

P_R's avatar
P_R
Frequent Visitor
7 years ago

Thank you for your reply. I think this will work for the question i asked. I have few more bits after getting the measure which is throwing me error. My question remains the same.

 

Your solution provides me a mesaure that is calculating amount on the salespersonsalesteam table but is there a way i can create a measure which shows corresponding Sales Group in Order table for each row?

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi P_R

    Sorry for inconvenience.

    I find a workaround.

    Create the relationships like this

     

    Create measures

    Start =
VAR condition1 =
    CALCULATE (
        MAX ( SalesPersonSalesTeam[ExpirationDate] ),
        ALLEXCEPT ( SalesPersonSalesTeam, SalesPersonSalesTeam[SalesPersonid] )
    )
RETURN
    IF (
        ISBLANK ( MAX ( [ExpirationDate] ) ),
        IF (
            MAX ( SalesPersonSalesTeam[StartDate] ) < condition1,
            condition1,
            MAX ( SalesPersonSalesTeam[StartDate] )
        ),
        MAX ( [StartDate] )
    )


end = IF(MAX([ExpirationDate])=BLANK(),DATE(2099,1,1),MAX([ExpirationDate]))

flag1 = IF(MAX('Order'[datestamp])<=[end]&&MAX('Order'[datestamp])>=[Start],1,0)

flag2 = IF(MAX(SalesPersonSalesTeam[SalesPersonid])=MAX('Order'[salespersonid]),1,0)

    Add 'SalesPersonSalesTeam' [SalesGroup] in the 'Order' table, add [flag1] and [flag2] in the Visual level filter.

    Please refer to my pbix->page2

     

     

     

    Best Regards

    Maggie