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

[P_R]

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 a column "Salespersonid" which identifies the salesperson for the order. In the SalesPersonTeam Table i have the salesperson and team it was assigned with startdate and enddate for the assignment. Below is my relationship:

Order Table:

datestamp	orderid	salespersonid	amount	unit
1/1/2018	117	4	        40	1
1/2/2018	118	2	        20	6
2/5/2018	119	1	        500	60
2/20/2018	120	2	        100	20
3/6/2018	121	1	        20	5
3/11/2018	122	3	        50	10
3/18/2018	123	1	        10	2

 SalesPerson Table:

Salespersonid	SalesPersonName
1	        John
2	        Mark
3	        Steve
4	        Chris

  SalesPersonSalesTeam Table:

SalesPersonSalesTeamID	SalesPersonid	TeamID	TeamName	StartDate	ExpirationDate	SalesGroup
11	                1	        1001	US	        01/01/2018	02/20/2018	West
12	                1	        1002	AUS	        02/21/2018	03/15/2018	South
13	                1	        1005	IND	        02/21/2018	null	        East
21	                2	        1001	US	        01/01/2018	02/01/2018	West
22	                2	        1003	UK	        02/02/2018	null	        North
31	                3	        1004	CAN	        01/01/2018	null	        North
41	                4	        1005	JPN	        01/01/2018	null	        East

Now i want to create a mesaure which i can use to show how much sales was done in different sales group by each sales person.  In TSQL i will write the query like:

SELECT * FROM Order O

LEF JOIN SalesPersonSalesTeam SPST ON SPST.Salespersonid = O.Salespersonid and O.datestamp BETWEEN SPST.StartDate AND SPST.ExpirationDate

I am learnign DAX and I tried RELATED, FILTER, CROSSFILTER but all are giving me error. I understand that i can create a sales group as calulated column in order table but i want to create it as measue because my order table have 16 Million records and may hinder the performance/storage. Is this the right approach to get corresponding SalesGroup for an Order? Can you Help?

[v-juanli-msft · Accepted solution]

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]

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]

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

[LivioLanzo]

Hello @P_R,

it appears you have Slow changing dimensions going on there. The approach I would suggest is to take the SalesPersonTeam and table and add an Index column to uniquely identify each row, you can either do this with SQL or with Power Query: 

Download the file: https://1drv.ms/u/s!AiiWkkwHZChHjzd1LWOZqbC3L-kk

= Table.AddIndexColumn(#"Changed Type", "SalesPersonKey", 1, 1)

= Table.TransformColumnTypes(#"Added Index",{{"SalesPersonKey", Int64.Type}})

Afterwards, you'd need to add this SalesPersonKey into the Order Table, in Power Query you can do it like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY5BDsAgCAT/wtkEFqu1b2n8/zfaRVsOkJiZXbxvgULdMKQIcL774BifMgu5J+d2DnkP7tqSXxEr0iwE24bbr0SSFQjFl1K1p4FdEmrbHPlLZ7zGlej5jJFG3RVY1+Z8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [datestamp = _t, orderid = _t, salespersonid = _t, amount = _t, unit = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"datestamp", type date}, {"orderid", Int64.Type}, {"salespersonid", Int64.Type}, {"amount", Int64.Type}, {"unit", Int64.Type}}),
    
    AddKeyColumns = Table.NestedJoin( ChangedType, "salespersonid", SalesPersonTeam, "SalesPersonid", "KeyTable", JoinKind.Inner),
    ExpandedKeyTable = Table.ExpandTableColumn(AddKeyColumns, "KeyTable", {"StartDate", "ExpirationDate", "SalesPersonKey"}, {"StartDate", "ExpirationDate", "SalesPersonKey"}),
    ReplacedValue = Table.ReplaceValue(ExpandedKeyTable,null,#date(9999, 12, 31),Replacer.ReplaceValue,{"ExpirationDate"}),
    
    SelectRows = Table.SelectRows(ReplacedValue, each [datestamp] >= [StartDate] and [datestamp] <= [ExpirationDate] )
in
    SelectRows

Then you can build your model:

and then add the matrix:

[zoloturu]

@P_R,

What if you create a calculated column with below logic?

Column = SUMX(
                FILTER('order',
                                [datestamp] >= [StartDate] 
                                && (
                                    [datestamp] <= [ExpirationDate].[Date] 
                                    || ISBLANK([ExpirationDate])=TRUE()
                                ) 
                                && 'Order'[salespersonid] = SalesPersonTeam[SalesPersonid]
                        )
                ,'Order'[amount]
            )

Then you will have a result like below:

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

[P_R]

In my question i mentioned that i understand that i can create custom column but since my order table have more than 16 million rows i was hoping if i can achieve this with a mesaure?

[P_R]

In my question i mentioned that i understand that i can create custom column but since my order table have more than 16 million rows i was hoping if i can achieve this with a mesaure?

[LivioLanzo]

Hi @P_R,

at the end it will still be a measure to calculate the numbers, and it will be a simple measure such as SUM(Orders[Amount])

The calculated column helps you in this case write an easier and faster DAX measure